Lookup or IF?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eucalypta - 08 Feb 2008 12:54 GMT

Dear all,
Looking for your kind help on the below:

In a spreadsheet I have the following cols and rows:
Col B Col E Col F Col G Col L Col Q
Date Artnr Pcs Price Order# Formula
(=IF(AND(B5<NOW()-60;L5="");"Alarm!!!";""))

What I am looking for is the following:
if Alarm is true I would like Excel to show me the art#, pcs and price in a
separate worksheet. Should I use IF or Lookup?
Thanking you in advance,
KG, Eucalypta

Reply to this Message

Teethless mama - 08 Feb 2008 13:35 GMT

=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(..........))

> Dear all,
> Looking for your kind help on the below:
[quoted text clipped - 9 lines]
> Thanking you in advance,
> KG, Eucalypta

Reply to this Message

Eucalypta - 08 Feb 2008 14:31 GMT

I am not sure I understand what you mean. Vlookup(.....). Lookup what?
KG, Eucalypta

> =IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(..........))
>
[quoted text clipped - 11 lines]
> > Thanking you in advance,
> > KG, Eucalypta

Reply to this Message

Max - 08 Feb 2008 16:12 GMT

One way which does it for your orig. posting

Illustrated in this sample:
http://www.freefilehosting.net/download/3bjj4
eucalypta.xls

Source data is assumed in Sheet1, as posted, data from row5 down

In Q5:
=IF(B5="","",IF(AND(B5<TODAY()-60,L5=""),"Alarm!!!",""))

In R5:
=IF(Q5="Alarm!!!",ROWS($1:1),"")
Select Q5:R5, copy down to cover the max expected extent of source data

Then in another Sheet2,

In B2:
=IF(ROWS($1:1)>COUNT(Sheet1!$R$5:$R$100),"",
INDEX(Sheet1!E$5:E$100,SMALL(Sheet1!$R$5:$R$100,ROWS($1:1))))
Copy B2 across to D2, fill down as far as required. Adapt to suit. Cols B to
D will return the required Art#, PCs & Price which satisfies the alert
criteria monitored in Sheet1's cols Q & R.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message