Autofilter

[Chris]

Hi,

I would like to setup an Autofilter which filters a column
based on the result of a lookup. How do I set the
criteria to be a cell reference?

Cheers

Chris

 

[Debra Dalgleish]

You could add a column to the table, and use it to check the row value
against the result of the lookup. Then, filter on the new column, for
TRUE or FALSE.

For example, with the lookup value in cell L1, enter the following
formula in cell K2: =$L$2>=H2
and copy down to the last row of data

 

[Dave Peterson]

I think you'll need some code for this.

I put some test data on a worksheet (A2:I30) and filtered it.

Then I put a =vlookup() formula in A1.

Then I rightclicked on the worksheet tab and selected view code. I pasted this
in the codewindow:

Option Explicit
Private Sub Worksheet_Calculate()
Static OLDValue As Variant

On Error GoTo errHandler:

If IsEmpty(OLDValue) _
Or OLDValue <> Me.Range("A1").Value Then
OLDValue = Me.Range("a1").Value
Application.EnableEvents = False
'it's changed!
'show all the data????
If Me.FilterMode Then
Me.ShowAllData
End If
Me.AutoFilter.Range.AutoFilter field:=1, Criteria1:=OLDValue
End If

errHandler:
Application.EnableEvents = True
End Sub

Each time that worksheet recalculates, this code starts. If the value in A1
changed, it shows all the data, then filters by the first column using that
value.

If the formula returns "#n/a", it just jumps to the errHandler and leaves the
existing filter shown.

You could add a check if it's important to watch out for that, too.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you want to learn more about what events are:

Chip Pearson has some notes about events at:
http://www.cpearson.com/excel/events.htm

David McRitchie also has notes at:
http://www.mvps.org/dmcritchie/excel/event.htm