Since you posted in the Programming forum, I attached a vba solution.
NOTE: that file DOES contain macros, but they do NOT auto-execute.
Here's the explanation of how it works:
Assumptions:
The DATA sheet contains your data in cells A1:H30.
The BIRTHDAYS sheet is where you want the extracted data to be displayed
Using the BIRTHDAYS sheet:
<Insert><Name><Define>
Names in workbook: BIRTHDAYS!Database
Refers to: =Data!$A$1:$H$30
Using the BIRTHDAYS sheet:
I1: StartMthDay
I2: =--TEXT(Data!$H2,"mmdd")>=--TEXT(Data!$P$5,"mmdd")
J1: EndMthDay
J2: =--TEXT(Data!$H2,"mmdd")<=--TEXT(Data!$Q$5,"mmdd")
<Insert><Name><Define>
Names in workbook: BIRTHDAYS!Criteria
Refers to: =BIRTHDAYS!$H$1:$J$2
Still using the BIRTHDAYS sheet:
<Insert><Name><Define>
Names in workbook: BIRTHDAYS!Extract
Refers to: =BIRTHDAYS!$A$1:$C$1
(Notice: you are on BIRTHDAYS, and creating a Sheet-level range name, but
the referenced range is on the DATA)
The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.
Now...set up the Advanced Data Filter:
<Data><Filter><Advanced Filter>
Select: Copy to another location
List Range: (press F3 and select Database)
Criteria Range: (press F3 and select Criteria)
Copy To: (press F3 and select Extract)
Click [OK]
Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
....OR...if you're feeling a bit ambitious...
You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: Insert>Module
Then, copy/paste this code into that module:
'---Start of Code-------
Sub PullMatchingData()
Range("BIRTHDAYS!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("BIRTHDAYS!Criteria"), _
CopyToRange:=Range("BIRTHDAYS!Extract"), _
Unique:=False
End Sub
'---End of Code-------
To run the code....just click the [Click to pull matching data] button
OR
<Tools><Macro><Macros> (or press [Alt]+[F8])
Select and run: PullMatchingData
In the attached workbookTo test, change the criteria values and run it again.
Is that something you can work with?
Bookmarks