CUSTOM FUNCTION APPROACH (Autofilter)

http://www.ozgrid.com/News/nov-2006.htm

CUSTOM FUNCTION APPROACH

The 1st one we will use is a custom Excel function. This will not only flag the correct criteria field but also tell us what the criteria is. Let's assume the table to filter occupies A1:E1000 with A1:E1 being headings. First we should always have at least 3 rows above any table in Excel. Select rows 1:3 and go to Insert>Rows. This will insert 3 rows, or as many as you select. We will use at least 1 row to display the criteria in the appropriate column. Why at least 3 rows then? Good practice is the answer. This way we can set up criteria, if need be, for Advanced Filter.

CRITERIA FUNCTION CODE

Below is the code that must be added to the Workbook, or an Excel Add-in. To add the code to a Workbook go to Tools>Macro>Visual Basic Editor (Alt+F11) then to Insert>Module and paste in the code below;

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As
String, strCri2 As String
Application.Volatile
With
Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)
If Not .On Then Exit Function
strCri1 = .Criteria1
If .Operator =
xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr
Then
strCri2 = " OR " & .Criteria2
End If
End With
End With

AutoFilter_Criteria = _
UCase(Header) & ": " & strCri1 &
strCri2
End Function

Note the use of Application.Volatile in the code. This will ensure our function updates whenever the AutoFilter criteria changes. Ok, ensure you have AutoFilter applied to your table. If you don't, the function will return #VALUE! Now, in, say A1 Enter =AutoFilter_Criteria(A4) (A4 is the 1st heading) and copy across as many columns as you have headings. Now simply filter by any criteria and the function will display your criteria in the relative column cell.