"danioma" wrote:
Max, You have been a GREAT help. I appreciate it!
You're welcome !
if I insert rows at the top, the formula does not work.
How would I keep it from doing that?
( I have to enter this formula in the middle of both sheets)
Try adapting along the lines of this revised construct, with the criteria
col placed instead in the source sheet: X, and with ROWS($A$x:Ax) replacing
ROW(A1) for the extract formulas in Y (depending on where the top left cell
of the extract range is)
Sample revised construct is available at:
http://www.savefile.com/files/5926851
Extract Unpaid Invoices into other sheet_1.xls
In X,
Assume data starts in row5 down
Put in D5: =IF(C5="","",IF(C5="Not Paid",ROW(),""))
Copy down to say, D100, to cover the max expected extent of source data.
(Leave D1:D4 empty)
In Y,
Assume the extracts are to start in row8 down, say
Put in A8:
=IF(ISERROR(SMALL(X!$D:$D,ROWS($A$8:A8))),"",INDEX (X!A:A,MATCH(SMALL(X!$D:$D,ROWS($A$8:A8)),X!$D:$D, 0)))
Copy A8 to B8, fill down to B103
(cover the same extent as in the criteria col D in X)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---