Creating a List that automotically filters by criteria

[futbol1097]

I have 2 worskheets in my sprdsht. - "Data" and "Analysis". What I want to be able to do is create a list in 'Analysis' that looks up dates on "Data" and only retrieves those items that fall within a given date range.
For example, On "Data", Data range is A1:B1000.
Column A is names:
Jon
Julie
Aaron
Hank
Sam
Peter
Tori

Column B are corresponding dates with the names:
8/13/07
8/10/07
7/6/07
5/3/07
8/9/07
7/29/07
2/13/07

I need to create a formula list that will compare all dates in Column B and return the names in Column A that have dates between now and the last seven days.
"Data" pulls in its info via SQL database so I am not doing any sorting or formulas on this tab. There are approximately 1,000 different names and corresponding dates so the formula in "Analysis" will need to process all of this info and filter out dates that didn't occur in the last 7 days. I need the names that meet the criteria to List on "Analysis" so I can edit them. This is a spreadsheet that will be updated every few days so it needs to be adaptible (i.e. when I change the current date or Now() it updates automatically)

Thanks,
R

 

[wisewood]

sh2.Range("A2:Z5000").ClearContents

Yes, the above code clears the contents of the range A2:Z5000, which I suppose you could make a dynamic range, having it delete all the rows where there is a value in the A column?

That way, if you put your totals in a column other than A, it will be left.

??