Autofilter based on cell value

[Alex Delamain]

Is it possible to drive the auto filter using a cell value (withou
using VBA)?

I am developing a template to analyse trends. The historical data i
pasted onto one sheet in a standard format. This sheet holds the arra
names etc to accomodate however much data there is.

On another sheet I have graphs and summary data in a report format.

What I want to be able to do is select a product code on the repor
sheet. This should then be used to filter the data so the report onl
shows information for that product.

If it can only done with VBA please will someone explain how to do i
in words of two syllables (preferably less) because so far I hav
avoided learning anything about VBA

 

[Dave Peterson]

I think you'll need VBA.

I used this setup:

I created two worksheets--Report and HistoryData.

I put my test data in A1:I50 and applied a filter in the HistoryData worksheet.

Then I used a cell (A1) on the Report worksheet as my cell that filters the
historydata worksheet data (by column A).

Rightclick on that report worksheet tab and select view code. Paste this into
the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

With Worksheets("historyData")
If .AutoFilterMode = False Then
MsgBox "Please apply Data|Filter|Autofilter to HistoryData"
Exit Sub
End If

If .FilterMode Then
.ShowAllData
End If

With .AutoFilter.Range
If Application.CountIf(.Columns(1), Target.Value) = 0 Then
MsgBox "not in the table"
Else
.AutoFilter field:=1, Criteria1:=Target.Value
End If
End With
End With
End Sub

Modify the address (A1) and the worksheet name (historyData) to match your
configuration.

If the product is not in column 1 of the filtered range, you'll have to change
these two things, too:

..Columns(1)
and
field:=1


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