Nov 13 2020 02:54 PM
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
Nov 14 2020 05:57 AM - edited Nov 14 2020 05:59 AM
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
Nov 14 2020 06:30 AM
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?
Nov 14 2020 06:46 AM
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?
Nov 15 2020 05:54 AM
Nov 15 2020 01:34 PM
Solution@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.
Nov 16 2020 04:39 AM
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
Nov 16 2020 06:34 AM
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
Nov 16 2020 06:48 AM
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
Nov 16 2020 06:56 AM
"...="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
Nov 16 2020 07:01 AM
Nov 15 2020 01:34 PM
Solution@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.