Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down list to run Auto filter on another sheet
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down list to run Auto filter on another sheet
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 "Mitch4" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down list to run Auto filter on another sheet
Thanks JMay for the response. I was hoping not to have to use macros but I
guess I will. "Mitch4" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NO values in Auto-filter drop-down | Excel Discussion (Misc queries) | |||
Excel Auto Filter will not show all items in the drop down - why? | Excel Discussion (Misc queries) | |||
Auto Filter - drop down (increase text size) | Excel Discussion (Misc queries) | |||
Why do I not see all my data when I use the auto filter drop down | Excel Discussion (Misc queries) | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) |