Conditional List

[Wayne Burritt]

Hi: I have many records arrayed horizontally. I have a list of symbols in
column E, a list of dates in column G, and a "Yes" or "No" for each record
in column H. What I would like is for excel to find all the "Yes" records
and the and then look at the corresponding date. If the date is within 1
week of today, I would like excel to make a list of those records, but just
return the symbol and the date. Any ideas? thanks. Wayne

 

[Dave Smith]

One way:

E F G H
2 @ 9/1/2003 Yes
3 # 9/5/2003 No
4 $ 9/15/2003 Yes
5 % 9/15/2003 No

Put this formula is I2 and copy down:

=IF(AND(ABS(INT(G1)-INT(NOW()))<=7,H1="Yes"),ROW(),"")

Put this formula in J2 and copy down:

=IF(ISNUMBER(SMALL($I$2:$I$5,ROW(1:1))),INDIRECT("E" &
SMALL($I$2:$I$5,ROW(1:1)),TRUE),"")

Put this formala in K2 and copy down:

=IF(ISNUMBER(SMALL($I$2:$I$5,ROW(1:1))),INDIRECT("G" &
SMALL($I$2:$I$5,ROW(1:1)),TRUE),"")

Format column K with your desired date format.

HTH

-Dave