MrExcel Publishing
Your One Stop for Excel Tips & Solutions

AutoFilter Question


Posted by Chris on December 07, 2001 4:38 PM

I do test score analysis for a school district (among other things). I have developed a test report using AutoFilter and the Subtotal function that allows the teachers and administrators to study how various groups of students are doing on the standardized state test. I have the report on one worksheet and the student data in another.

I need to find a way to have a cell (or cells) on the report page show which AutoFilter colums are active and which values of each autofilter are selected without user intervention. For instance, if the user had AutoFilters active for Gender (Male), Grade (3), and Ethnicity (Amerindian), the report worksheet would list "Male, 3, Amerindian".

I'm not sure that this is possible, but figured that this is the place to find out for sure.

Thanks for any assistance you can provide.

Chris


Posted by Tom Urtis on December 07, 2001 5:00 PM

Chris:

It's possible to do this a few different ways with an auto filter, including but necessarily limited to, VBA code with input boxes linked to cells that will display the criteria and execute the filter.

Before all that though, have you considered using an advance filter instead of an auto filter? One advantage is that the advance filter criteria range would already show the user what criteria was requested, which you say you want. Also, an advance filter is more flexible, as it can handle criteria for arithmetic calculations, multiple "Or" criteria, and for a larger dataset. Finally, you can extract a copy of the rows meeting the criteria to another location, such as to the Student's worksheet.

Just a few thoughts, so if I'm off base with the meaning of your question then please re-post.

Tom Urtis

Posted by Vincentio on December 07, 2001 5:32 PM


Here's a UDF.
It returns the AutoFilter criteria for the specified cell's column.
If there is no filter applied to the column or if the specified cell is not in an AutoFilter range, it returns a blank string.

Function FCrit(rng As Range) As String
Dim filt As String
On Error GoTo NoCrit
With rng.Parent.AutoFilter
If Intersect(rng, .Range) Is Nothing Then GoTo NoCrit
With .Filters(rng.Column - .Range.Column + 1)
If Not .On Then GoTo NoCrit
filt = .Criteria1
Select Case .Operator
Case xlAnd
filt = filt & " AND " & .Criteria2
Case xlOr
filt = filt & " OR " & .Criteria2
End Select
End With
End With
NoCrit:
FCrit = filt
End Function

Posted by Bariloche on December 07, 2001 7:58 PM

Chris,

Have you considered using a pivot table? I haven't seen your data, of course, but I'd guess that it would work quite nicely in a pivot table. I assume that your "data" field would be test score.

I'd sure give it a try if I were you. If you've never used a pivot table, the data analysis potential it has will impress you. (IMO)

enjoy

Posted by Chris on December 10, 2001 12:53 PM

Thanks to all for the input. It sounds like I wasn't as clear as I might have been. The bottom line for this is that I don't want the user to have to label the report page as to what filters are active, since many of them have no understanding of Excel at all - not that I'm much better; however....

I seems that Vincentio's solution is what I am looking for but, even though I have done many things with Excel, I haven't done anything with VBA.

Is there a tutorial that would help me understand what the ranges, cell references, etc. mean, as well as how best to call the function somewhere?

Sorry to be so dumb.

Thanks again.

Chris

Posted by Vincentio on December 10, 2001 3:14 PM

To find out how to install the function into your workbook, have a look at http://www.mrexcel.com/tip038.shtml

To use the function on the same worksheet as the filter (let's say for Column A), type in a cell either =fcrit(A:A) or a specific range such as =fcrit(A1:A100).
If the first formula is used, it cannot be put in a cell in the same column as the filter (a circular ref. arises).

To use the function in a sheet other than the one with the filter :- =fcrit(SheetName!A:A)
(Note : This can be put in Column A if you wish)

I have added one line to the function so that the results update automatically as and when filter settings are changed :-

Function FCrit(rng As Range) As String
Application.Volatile
Dim filt As String
On Error GoTo NoCrit
With rng.Parent.AutoFilter
If Intersect(rng, .Range) Is Nothing Then GoTo NoCrit
With .Filters(rng.Column - .Range.Column + 1)
If Not .On Then GoTo NoCrit
filt = .Criteria1
Select Case .Operator
Case xlAnd
filt = filt & " AND " & .Criteria2
Case xlOr
filt = filt & " OR " & .Criteria2
End Select
End With
End With
NoCrit:
FCrit = filt
End Function

Posted by Chris on December 12, 2001 4:24 PM

Vincentio (and everyone else who tried to help),

Thank you! Vincentio's solution worked perfectly. It will be a great resource for our folks.

Chris