Drop Down list to run Auto filter on another sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to have a drop down list be used to be able to select the
criteria for the auto filter on another worksheet. Is this possible?
 
Yes,
Here's a small example
On Sheet2 I have an auto-filtered table (with headers) in the Range B6:E20
On My Sheet1 cell A1 I entered by selecting Data, Validation, List, Source =
Mycriteria

In Range $M$1 down is the dynamic rangename myCriteria
Create a new Rangename MyCriteria and in the refersto box enter:

=OFFSET(Sheet1!$M$2,0,0,COUNTA(Sheet1!$M:$M)-1,1)

Two macros are necessary to achieve what you want.

From Sheet1 run Macro1:

Sub Macro1()
'
' Macro1 Macro ' This macro does an Advanced-Filter returning the Unique
'records in the Second field of the Autofilter
(column3)
'pastes the unique records in Cell $M$1 (header)
downwards
' Macro recorded 7/21/2007 by Jim May
'
lrow = Cells(Rows.Count, 3).End(xlUp).Row
With Sheets("Sheet2").Range("C6:C" & lrow)
.AdvancedFilter Action:=xlFilterCopy, CopyToRange _
:=Sheets("Sheet1").Range("$M$1"), Unique:=True
End With
End Sub

After running Macro1 - you should then go to Cell A1 and make your
selection from the most recnet update of all the possible Col2 fields
in the autofilter on sheet2.

Once you have selected which record you want (in cell A1), then Run:

Macro2 'this macro applys the auto-filter on sheet2 from the value in
Sheet1 A1
Sub Macro2()
Sheets("Sheet2").Activate
Selection.AutoFilter Field:=2,
Criteria1:=Sheets("Sheet1").Range("A1").Value, _ Operator:=xlAnd
End Sub

Hope this helps,
Post back if there is a problem

Jim May
 
Back
Top