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?

Thanks!!!


--
danioma
------------------------------------------------------------------------
danioma's Profile: http://www.excelforum.com/member.php...o&userid=35112
View this thread: http://www.excelforum.com/showthread...hreadid=548678

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:


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?

Thanks!!!


--
danioma
------------------------------------------------------------------------
danioma's Profile: http://www.excelforum.com/member.php...o&userid=35112
View this thread: http://www.excelforum.com/showthread...hreadid=548678

"danioma" wrote:
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?

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,M ATCH(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 !)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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!


--
danioma
------------------------------------------------------------------------
danioma's Profile: http://www.excelforum.com/member.php...o&userid=35112
View this thread: http://www.excelforum.com/showthread...hreadid=548678

"danioma" wrote:
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!! :)

One way is to try the suggested non-array formulas play in my response <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks for the help - I REALLY appreciate it.
I am having difficulty still - is there anyway you could forward me an
example?


THANKS AGAIN!!


--
danioma
------------------------------------------------------------------------
danioma's Profile: http://www.excelforum.com/member.php...o&userid=35112
View this thread: http://www.excelforum.com/showthread...hreadid=548678

"danioma" wrote:
Thanks for the help - I REALLY appreciate it.
I am having difficulty still - is there anyway you could forward me an
example?

Here's a sample with an implemented construct:
http://www.savefile.com/files/1466410
Extract Unpaid Invoices into other sheet.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Just one more Q - now, if I instert 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)


--
danioma
------------------------------------------------------------------------
danioma's Profile: http://www.excelforum.com/member.php...o&userid=35112
View this thread: http://www.excelforum.com/showthread...hreadid=548678

"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
---

Hi Max,

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


--
danioma
------------------------------------------------------------------------
danioma's Profile: http://www.excelforum.com/member.php...o&userid=35112
View this thread: http://www.excelforum.com/showthread...hreadid=548678

Glad to hear that !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"danioma" wrote:

Hi Max,

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


--
danioma
------------------------------------------------------------------------
danioma's Profile: http://www.excelforum.com/member.php...o&userid=35112
View this thread: http://www.excelforum.com/showthread...hreadid=548678