Copy and üasting the values from one sheet to another by macro or by formulation

Question

Chaturvedi, Santosh 340

Hello

I want a formulation

to copy the values from table "E" & "F" from oner sheet left side to

Another excel sheet left side in Cell B5 & C5 onwrds multiplied with slected cell value B4 & C4 ( This will change as per req.)

"-" from first sheet shall be deleted in the new sheet

Also sort the the values in B5 & C5 from higher to lower

(Example 11.66 & 1, 11.23&4 ,11.12&,19, 11.01 &32---

User's image

0 comments

Answer accepted by question author

0 additional answers

Answer 1

Thank you for describing the layout

Enter this single formula in cell B5 of the destination sheet, and it will spill both columns and all rows automatically:

=LET(src, FILTER(Sheet1!EE5:FF1000, (Sheet1!EE5:EE1000<>"-")(Sheet1!EE5:EE1000<>"")), SORT(srcHSTACK(BB4,CC4), 1, -1))

A few notes on adapting it:

Replace Sheet1 with your source sheet name, and adjust the row range to cover your data.
FILTER removes the "-" rows; add further conditions inside the same brackets, each wrapped in its own parentheses and joined with *.
SORT with sort_index 1 and sort_order -1 sorts by the first column from highest to lowest; change the 1 to 2 to sort by the second column instead.
HSTACK(BB4,CC4) multiplies column E by B4 and column F by C4. If the second column is an identifier that should not be scaled, tell me and I will adjust the formula.
Leave B5:C5 downward empty so the result can spill; a #SPILL! error means something is blocking that area.

This requires a Microsoft 365 version of Excel.

Refer here for Official Microsoft guidance about these functions:

I hope this helps. Please feel free to reply if you need further assistance.