Filtering Question

[SZBELL]

I have auto filtered Column S. Cell S1 has the drop down list showing my criteria. I need to control S1 selection from A1 on the same sheet. How do I make this work?

[Jakobshavn]

Is A1 an entry or a formula?

[SZBELL]

its an entry.

[Jakobshavn]

I have attached a sample based upon a macro solution. Load the file, enable macros, and then make an entry in cell A1. We can adapt this to your actual needs.

[SZBELL]

Perfect. I can place a list in A1 and it will control my filter. What are the steps to get this macro into my file. Copy Paste?

[Jakobshavn]

First here is the macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = "" Then
    Range("$S$1:$S$85").AutoFilter Field:=1
Else
    Call Macro1(Target.Value)
End If
Target.Select
Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

[SZBELL]

https://docs.google.com/open?id=0B8f...G9RdVlMOG5XQk0

Please see attached error. Thanks

[Jakobshavn]

Here is the missing macro:

Sub Macro1(s As String)
'
' Macro1 Macro
'

'
    Columns("S:S").Select
    Selection.AutoFilter
    ActiveSheet.Range("$S$1:$S$85").AutoFilter Field:=1, Criteria1:=s
End Sub

This macro goes in a standard module:


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

This macro will be called from the first one that you have already installed.

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Macros must be enabled for this to work!

[SZBELL]

** FINALLY!, I figured it out, thanks for your help. Do you know of a code for a reset feature, that would clear all filters?

[Jakobshavn]

If you empty A1, the filter will be "cleared" (that is all rows will be displayed)

[Jakobshavn]

tomorrow...................

[SZBELL]

How do I add additional cells like a1? Do i have to create another Macro?

[SZBELL]

Let me ask this a different way. I would like to add the same filtering ability but with a different column in a different cell. What do I have to add to do this, with out losing what we already have.

[Jakobshavn]

It would involve a modification to the existing macro. Of course, we need the details - which trigger cells and what filter functions.

[SZBELL]

filtering cell would be d3, with Y:9 - Y201 data.

[SZBELL]

I cant seem to filter both cells..

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B3,F5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = "" Then
    Range("T9:$U$297").AutoFilter Field:=1
    Range("T9:$U$297").AutoFilter Field:=2

Else
    Call MACRO1(Target.Value)
End If
Target.Select
Application.EnableEvents = True


End Sub

Sub MACRO1(s As String)
'
' Macro1 Macro
'

    Columns("T:T").Select
    Selection.AutoFilter
    ActiveSheet.Range("T9:$U$297").AutoFilter Field:=1, Criteria1:=s
'
    Columns("U:U").Select
    Selection.AutoFilter
    ActiveSheet.Range("T9:$U$297").AutoFilter Field:=2, Criteria1:=s
    
    
End Sub

[SZBELL]

is this possible?