Look up Help!

D

danioma

I am having trouble with a lookup formula and was hoping someone coul
help me. I have one list of invoices one on sheet and a column next t
them where it says paid or not paid. I am trying to pull only the "no
paid" amounts into another sheet but want to do this without the zeroe
or spaces in between rows. Any ideas how to do this?

Thanks!!
 
M

Marcelo

Hy

one way to do it is auto filter, select the data, copy and paste in another
spreadsheet

hope this help
Regards from Brazil
Marcelo

"danioma" escreveu:
 
M

Max

danioma said:
I am having trouble with a lookup formula and was hoping someone could
help me. I have one list of invoices one on sheet and a column next to
them where it says paid or not paid. I am trying to pull only the "not
paid" amounts into another sheet but want to do this without the zeroes
or spaces in between rows. Any ideas how to do this?
Click to expand...

One way using non-array formulas ..

Assume source data is in sheet: X,
in cols A to C, with the key status col = col C (with the "Not Paid" text)
data from row 2 down to a max expected row 100 (say)

In another sheet: Y (say)

Put in A2
=IF(ISERROR(SMALL($C:$C,ROW(A1))),"",INDEX(X!A:A,MATCH(SMALL($C:$C,ROW(A1)),$C:$C,0)))
Copy A2 to B2

Put in C2: =IF(X!C2="","",IF(X!C2="Not Paid",ROW(),""))
(Leave C1 empty)

Select A2:C2, fill down to C100
(cover the expected data extent in X)

Cols A & B will auto-return only the lines with "Not Paid" in X,
with all results neatly bunched at the top (w/o spaces !)
 
D

danioma

HI, Thanks for the response..

The only thing is I need to have it as a formula to re-use each month.
I know there must be a way to do this!! :) Thanks again
 
M

Max

danioma said:
The only thing is I need to have it as a formula to re-use each month.
I know there must be a way to do this!! :)
Click to expand...

One way is to try the suggested non-array formulas play in my response <g>
 
D

danioma

Max,
You have been a GREAT help. I appreciate it!

Just one more Q - now, if I instert rows at the top, the formula doe
not work.
How would I keep it from doing that?

( I have to enter this formula in the middle of both sheets
 
M

Max

danioma said:
Max, You have been a GREAT help. I appreciate it!
Click to expand...

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)
Click to expand...

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)
 
D

danioma

Hi Max,

Just wanted to thank you again for all your help. With all of you
feedback, I finally was able to get it to work and was able to build
tool that is extremely helpful. Thanks again
 
Top