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

 

[futbol1097]

I have been trying to work with Lists in Excel. The problem I run into is that it is hard to create a list that links to data on another worksheet. The normal List function only works with data in the same worksheet.

- R

 

[wisewood]

This will copy each row from Data and paste to Analysis, where column B is greater than, or equal to NOW() - 7 ... ie within the last 7 days.

Hope this helps.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Limit As Long, c As Long, d As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Data")
Set sh2 = Sheets("Analysis")
Limit = sh1.Cells(Rows.Count, 1).End(xlUp).Row
d = 2
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
sh2.Range("A2:Z5000").ClearContents
For c = 1 To Limit

    If sh1.Cells(c, 2) >= Now - 7 Then  
        sh1.Rows(c).Copy Destination:=sh2.Rows(d)
        d = d + 1
    End If

Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[futbol1097]

That's great Wisewood. 2 quick questions -

1) instead of Now(), is it possible to have that be an actual date that I input into A1 on "Analysis"? I don't want this to update everytime I open up the spreadsheet, which Now() does, but only when I manually change the date entered into cell A1.

2) My info on "Data" actually is actually located in Column B (names) and column G (dates) - i said A & B for simplicity. I will need to return the info in Column B, Column D, Column F.

Thanks,
R

 

[wisewood]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Limit As Long, c As Long, d As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Data")
Set sh2 = Sheets("Analysis")
Limit = sh1.Cells(Rows.Count, 1).End(xlUp).Row
d = 2
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
sh2.Range("A2:Z5000").ClearContents
For c = 1 To Limit

    If sh1.Cells(c, 7) >= sh1.Range("A1") - 7 Then 
        sh1.Rows(c).Copy Destination:=sh2.Rows(d)
        d = d + 1
    End If

Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

This code;

If sh1.Cells(c, 7) >= sh1.Range("A1") - 7 Then

... is the part that deals with the range. sh1.Cells(c, 7) tells it to look in the seventh column of Data - the seventh column being G. The next bit should be self explainatory.

 

[wisewood]

Oh yeah - and to make the Analysis page printer friendly - when it clears the contents of that page to update it with new information, it leaves row 1, so you can put some headers on there if you want, and they wont get wiped out.

 

[futbol1097]

Can I embed this table to work within a certain part of "Analysis"? For instance, I want Rows 1 - 25 to be other parts of the spreadsheet not related to this data (graphs, miscell. tables). I want the actual list that we are creating here to begin on Row 30 - onwards. Is that possible?

Also, what to "c" and "d" denote in your code above?

I really appreciate this,
R

 

[wisewood]

c is a variable representing the row you're on within the loop for each row, and d is a variable represending the row on the analysis page, d=2 tells it to start at row2, and then for each row it copies, it adds 1 to the value of d, so each subsequent row is pasted onto the next line.

You can specify the start and stop points for the script to use as its range. I will sort it and put the code back up for you.

Starting on row 30 - is there a maximum row number you would like to use?

 

[wisewood]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Limit As Long, c As Long, d As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Data")
Set sh2 = Sheets("Analysis")
Limit = sh1.Cells(Rows.Count, 1).End(xlUp).Row
d = 2
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
sh2.Range("A2:Z5000").ClearContents
For c = 30 To Limit
'For c = "The Row You Want To Start From" To Limit

    If sh1.Cells(c, 2) >= sh1.Range("A1") - 7 Then
        sh1.Rows(c).Copy Destination:=sh2.Rows(d)
        d = d + 1
    End If

Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[futbol1097]

OK, I get it. Well, the number of data points in the table in "Analysis" are going to fluctuate based on how the data changes in "Data". So maybe today our table will show 15 names that meet criteria (so will go from row 30 to row 45). Next time it could be 30 names.

You mentioned that an Update clears the content of the page. Besides name, I will be pulling in a column with dollar amounts as well from the Data page. If I put in an average formula below the table we create for these $, will it be wiped out whenever I update and the number of rows fluctuates as more or less data points meet the 7-day criteria?