AutoFilter Question

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

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

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

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

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

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