If a cell contains a value then copy row to a new sheet.
- Thread starter JAeEM
- Start date
Peter Bartholomew
Well-Known Member
Is this the sort of thing you require?
The data source is a table. I have selected records on the basis of a flag and either transferred values cell at a time into another table or, alternatively, transferred the whole lot using a single multi-cell array formula.
The key is the named formula 'pointer' that contains the record indices of the records flagged to be copied. The array is consolidated using SMALL and INDEX is used to transfer the cell content.
The data source is a table. I have selected records on the basis of a flag and either transferred values cell at a time into another table or, alternatively, transferred the whole lot using a single multi-cell array formula.
The key is the named formula 'pointer' that contains the record indices of the records flagged to be copied. The array is consolidated using SMALL and INDEX is used to transfer the cell content.
Joel Mejia
New Member
this is the formula i'm currently using
(IF(ISNUMBER(SEARCH("ACQUIRED",CURATION!R576)),CURATION!A576:Q576,""))
"ACQUIRED" IS THE "TRIGGER" THAT COPIES THE DATA TO THE NEXT TAB.
Instead I would like to use any value in the cell and have the whole row copy to the destination sheet.
(IF(ISNUMBER(SEARCH("ACQUIRED",CURATION!R576)),CURATION!A576:Q576,""))
"ACQUIRED" IS THE "TRIGGER" THAT COPIES THE DATA TO THE NEXT TAB.
Instead I would like to use any value in the cell and have the whole row copy to the destination sheet.
Joel Mejia
New Member
Thank you all so much for your responses! You guys are gods!
Peter Bartholomew
Well-Known Member
I would recommend starting with a helper field like my 'Flag'. Only when you have both the condition and the data reads from the destination working is it worth considering 'clever' tricks to remove helper ranges (normally by nesting formulas but named formulas provide an alternative approach to the nested formula). In your case Flag is defined by a relative formula
= ISNUMBER(SEARCH("ACQUIRED",CURATION!R576))
= ISNUMBER(SEARCH("ACQUIRED",CURATION!R576))
Peter Bartholomew
Well-Known Member
I have updated the miniscule file to demonstrate how the 'flag' field can be changed to a formula to capture pretty much any selection criterion you may choose. An advantage of working with helper ranges is, once you have the formula working correctly, everything downstream should go through without further intervention (assuming it was correct in the first place 
).
).I am not sure what this is getting at. Could you explain?