Getting autofilter info

rline101

Board Regular
Joined
Dec 22, 2005
Messages
70
I have some data autofiltered. So eg I have 3 coluumns of grade, gender and house (for a sports carnival). So I select, say, grade to be 9, gender to be boys and house to be Yellow. Is it possible to have those three things "9", "Girls" and "Yellow" automatically returned in another cell nearby?

Thanks.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,255
Expanding on your example, assume you have a 3-column table with row 1 as the header row. In column A is your field for Grade, in column B is your field for Gender, and column C is your field for House.


Step 1
Stick this code into a standard VBA module:


Function FilterCriteria(Rng As Range) As String
Application.Volatile
'By Stephen Bullen (slightly modified by me for this post)
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
If ActiveSheet.AutoFilterMode = True Then
If Filter <> "" Then
FilterCriteria = Right(Filter, Len(Filter) - WorksheetFunction.Search("=", Filter))
End If
End If
End Function



Step 2

In cell F1 enter this formula to display the filter criteria for column A (Grade):
=FilterCriteria(A1)

In cell G1 enter this formula to display the filter criteria for column B (Gender):
=FilterCriteria(B1)

In cell H1 enter this formula to display the filter criteria for column C (House):
=FilterCriteria(C1)



Now when you AutoFilter that table, you will see the criteria in F1:H1 for respective field headers A1:C1.
 

Forum statistics

Threads
1,136,366
Messages
5,675,354
Members
419,564
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top