Forum Discussion

iapetustitan's avatar
iapetustitan
Copper Contributor
Nov 13, 2020
Solved

Can anyone sense check my formula? Copying cells between sheets

Hi.  I've got this formula to copy cell contents into another sheet on the same workbook: =IFERROR(INDEX('Sheet1'!$D:$D, TEXT(IF('Sheet1'!$C:$C="Yes",ROW('Sheet1'!$C;$C)),ROW())),"")   Now I'm on...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 15, 2020

    iapetustitan Thanks for the clarification. Not sure I fully understood the logic, but I'd suggest

    in second column

    =IFERROR(
     INDEX(
       Area_Running_Totals[Order Code],
       AGGREGATE(15,6,
             1/(Area_Running_Totals[[Add To Order?]:[Add To Order?]]="Yes")*
             (ROW(Area_Running_Totals[Add To Order?])-ROW(Area_Running_Totals[[#Headers],[Add To Order?]])),
             (ROW()-ROW(OrderForm[[#Headers],[Supplier Product Code]]))
       )
),"")

    and similar to the right.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 14, 2020

iapetustitan 

Malc, could you please be more close to your sample file - there is Sheet1 in it and there is the formula you try to use?

iapetustitan's avatar
iapetustitan
Copper Contributor
Nov 14, 2020

SergeiBaklan 

Sorry, I put that rather than the actual sheet name as I thought it would be helpful.


In the previously attached spreadsheet the formulas are housed in cell range C19:F38 (in the 'Order_Form' table in the Order Form sheet) and are written fully as:


=IFERROR(INDEX('East Stock Holdings'!$D:$D, TEXT(IF('East Stock Holdings'!$C:$C="Yes",ROW('East Stock Holdings'!$C;$C)),ROW())),"") 

 

the 'INDEX' part of the formulas changes slightly depending on the column they are in (the ranges used are $D:$D (as above), $E:$E, $G:$G and $H:$H).

Is that more helpful?

Resources