Here's one non-array formulas play
which could achieve exactly what you're after
A sample construct is available at:
http://cjoint.com/?chjj5BOkFe
Auto copy rows to another sheet_darkwood_wks.xls
Assume source data in sheet: X,
cols A to C, data from row2 down.
The criteria col = col E,
wherein the "Y", "N" will be tagged for the lines in X
In another sheet: YetToClear
With the same headers in A1:C1: Field1, Field2, Field3
Put in A2:
=IF(ISERROR(SMALL($D:$D,ROW(A1))),"",
INDEX(X!A:A,MATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))
Copy A2 to C2
Put in D2:
=IF(X!E2="","",IF(X!E2="N",ROW(),""))
(Leave D1 empty)
Select A2:D2, copy down to cover the max expected extent of data in X
The above will auto-return only the lines for cols A to C from X where col E
= "N", all lines neatly bunched at the top. If the tagging "N" is changed in
X to "Y", the particular line will then disappear from YetToClear, and the
remaining lines will "move up" (and vice versa, if the tagging is changed
from "Y" to "N")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"darkwood" wrote in
message ...
Here's my dilemma:
I have separate worksheets for different aspects of my budget (i.e.
spending money, groceries, gas, etc.) I also have a separate sheet that
shows items that have yet to clear my checking account, and this all
ties back into the main spreadsheet which shows the budget as a whole
and a bottom line of extra I have at the end of each month to put in
savings.
I have added a column to each sheet that will have a "Y" or "N" which
indicates if the charge listed in A=Merchant B= Date and C= amount
spent has cleared my account yet. If it has a "N", I would like those
3 columns (A4:C4 for example) to be copied over to the 'yet to clear'
sheet in the next blank row. Once it becomes a "Y", that row
disappears, so the remaining charges yet to clear are moved to the top
of the list.
Is this possible? Thanks in advance.
-Scott
--
darkwood
------------------------------------------------------------------------
darkwood's Profile:
http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=509218