Lookup and conditional formating

D

Dan

Trying to return the entire row from a corresponding sheet based on a range
of dates. How can this be accomplished?

The range is "today( ) to today( )-21". There will be multiple entries that
meet this criteria and will vary day by day.

The range of the data is E2:E500.

Thanks

Dan
 
M

Max

Dan said:
Trying to return the entire row from a corresponding sheet
based on a range of dates. How can this be accomplished?
The range is "today( ) to today( )-21".
There will be multiple entries that
meet this criteria and will vary day by day.
The range of the data is E2:E500.
Click to expand...

Here's a play using non-array formulas
which delivers exactly what you're after

A sample construct is available at:
http://www.savefile.com/files/6262913
Auto-Return Lines satisfying date range.xls

Assume source table in sheet: X, cols A to E,
headers in row1, data from row2 down,
dates in col E

In sheet: Y,

Identical headers placed in A1:E1

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

Put in F2:
=IF(AND(X!E2>=TODAY()-21,X!E2<=TODAY(),X!E2<>""),ROW(),"")

Select A2:F2, copy down to say, F500?
to cover the max expected extent of data in X
Format col E as date

Y will auto-return lines from X whose dates in col E satisfy:
"today( ) to today( )-21",
with all lines neatly bunched at the top
 
M

Max

I've lightly disregarded your subject line which
doesn't quite gell with your actual intents expressed ..
(eg: there's no conditional formatting involved <g>)
 
Top