I have three tabs, one has all the data and will be updated with new entries frequently. The other two tabs are labeled as completed and incomplete. I would like a formula that would export a row of cells from the main sheet to another sheet if the cell value within the row contains "Y" and to another if the cell value within the row contains "N". (For completed and incomplete)
Answers (1)
Spec's too murky for any precise procedure, but in general formulas don't change the structure of a sheet (eg move rows or filter). For that, there is vba. Sounds like you'd either want to bind the worksheet_selectionchange event (if it's automatic upon entry), or a plain button.
Personally, I've emulated some forms of restructuring using clever tricks of formulas, but it's pretty unclean and if anyone has to maintain or use it, a proper programming language is preferable.
As a fun exercise, for this "auto filter" I've created a column (A + B) in the main sheet, which increments by +1 when condition column is true: [A2]=IF(D2=1,A1+1,A1);
Then, in the 2 other sheets, a vlookup (plus blank obfuscation would look better) for every row, diffused along the same number of cols: [A1]=VLOOKUP(ROW(A1),$Main.$A:$E,COLUMN(A1)+2,0)
Since vlookup always returns the first matching row, this picks out the ones which trigger the indexing, per the requirement, regardless of any subsequent other group rows.