Forum Discussion
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 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
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.
10 Replies
- Hey folks, just bumping to keep this alive, hoping there is an answer out there. Thanks Malc
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.
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
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
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?
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?
