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.

iapetustitan's avatar
iapetustitan
Copper Contributor
Nov 14, 2020

I've gathered that the red text section in the formula is the problem (least I think it is)
=IFERROR(INDEX('Sheet1'!$D:$D, TEXT(IF('Sheet1'!$C:$C="Yes",ROW('Sheet1'!$C;$C)),ROW())),"")

 

But I'm drawing a big blank on how to fix it. 

Of course, the whole formula could be nonsense but it pulls the data I want across...just can't get it to set up as a continuous list.
Image attached to show the situation instead of re-attaching same workbook with options selected. (didn't mention before but the formula is applied across all the cells in the order_form table with edits in the ranges to match whats needed in each column).

 

Thanks

Malc

 

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