SOLVED

Can anyone sense check my formula? Copying cells between sheets

Copper Contributor

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 only about 75% content I actually know what the arguments are doing here (I threw in TEXT at a guess and it (kinda) worked).

The idea is to select a number of different cells from a large list of items and copy the required data into a specific range on the new sheet. 

The issue I'm having is that, while I can get the relevant cell to copy to the new sheet when I've selected "YES" in the related cell (drop down list) the thing only seems to work in the same row across both sheets.
E.g. I'f I've selected Yes in, say C19, then the required data copies to the new sheet but only in the relevant cell in row 19. If I hit Yes in row 19 and 22 then, again, data copies fine but only in those rows so there is a 2 row gap between the data. 
For clarity the range I wish to copy the relevant data to is the OrderForm table on the Order Form sheet.
Any help here is much appreciated. 
Cheers

Malc

10 Replies

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).

12.png

 

Thanks

Malc

@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?

@Sergei Baklan 

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?

Hey folks, just bumping to keep this alive, hoping there is an answer out there. Thanks Malc
best response confirmed by iapetustitan (Copper Contributor)
Solution

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

image.png

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.

@Sergei Baklan 

Excellent, thanks so much. Works like a charm.

I'm scrutinising the formula to get a better understanding of it, still not sure how the 'SMALL' function works with text but I also didn't know you could use normal operators (-, *, etc) with non-numeric data either (though I guess it's not technically the actual cell content that is super important in this fashion?).

 

Thanks again though, very much appreciated.

Malc

@iapetustitan 

SMALL actually means that we take 1st, 2nd, 3rd, etc. value for which [Add to Order] flag is "Yes". For example

row flag value

1               201      

2       Yes   302

3               103

4       Yes     55

5               105

 

first smallest position with flag will be 2; second smallest position is 4, etc. Returned values are 302 and 55 accordingly.

 

Didn't catch the question about arithmetic operations with texts - nope, as a rule that doesn't work, by why do you need them here?

5

@Sergei Baklan 

I meant in ref to the operators in the formula you supplied:
"...="Yes")*(ROW..." and "..(ROW()-ROW..."

Wouldn't have thought they would be used in a non-numeric sense. I'm learning though.

 

I see what you mean re the 'SMALL' function. That's a bit easier to understand than the examples I'd read online. Thanks.

 

Malc

@iapetustitan 

"...="Yes")  returns TRUE of FALSE which is equivalent of 1 or 0

ROW(Table[Column]) returns array of row numbers for the column (5,6,7,8,...)

ROW() returns row number (5,11,15, whatever)

 

I don't see here texts at all

 

I was meaning non-numeric in reference to the cell content.
That was my misunderstanding.
My only experience of using */+/-, etc. was with the SUM functions and numeric data in the cells.
I'm not looking to use it here was just commenting that I didn't now they could be used at all as my mind was on the cell content.
1 best response

Accepted Solutions
best response confirmed by iapetustitan (Copper Contributor)
Solution

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

image.png

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.

View solution in original post