Display Filter Selection

[Andre Basson]

Hi, Would like to know whether it is possible to display your selected choice when using Auto Filter - e.g. if you have chosen "All" it must display "All" in the cell you have chosen for this pupose - typically a cell in the header in the column being filtered. Tx Andre

 

[Stormseed]

Welcome to MrExcel.

Regarding your question: I don't think so, Andre.

 

[VoG]

Hello and welcome to MrExcel.

This came from John Walkenbach if I remember correctly. Insert this UDF in a standard module:

Function FilterCrit(Rng As Range) As String
    Dim Filter As String
    Filter = "{All}"
    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:
    FilterCrit = Filter
End Function

Then on your worksheet enter the formula

=filtercrit(A3)

where A3 is within the range being filtered.

 

[Stormseed]

Hello and welcome to MrExcel.

This came from John Walkenbach if I remember correctly. Insert this UDF in a standard module:

Function FilterCrit(Rng As Range) As String
    Dim Filter As String
    Filter = "{All}"
    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:
    FilterCrit = Filter
End Function

Then on your worksheet enter the formula

=filtercrit(A3)

where A3 is within the range being filtered

Hi Peter (I guess, I referred you by your real name ? and I hope it is ok with you ?)

This would prolly display the Filtered criteria in the cell where the formula has been entered, correct ? What if the user wants the selected filtered criteria to be displayed as its heading ? Is it possible ?

 

[Peter_SSs]

Note that there will be some issues here if using Excel 2007 because there are so many AutoFilter choices/combinations possible.

 

[Andre Basson]

Thank you for those quick responses, VoG and Stormseed. Will try VoG's suggestion and hope Stormseed is worng! A

 

[VoG]

Hi Peter (I guess, I referred you by your real name ? and I hope it is ok with you ?)

This would prolly display the Filtered criteria in the cell where the formula has been entered, correct ? What if the user wants the selected filtered criteria to be displayed as its heading ? Is it possible ?

Hi

I'm not sure quite what you mean ... In the example I gave, the formula could be entered in A1 to display the criteria being used.

Peter - good point re XL 2007.

 

[Stormseed]

hope Stormseed is worng

me too !

I hope that I go wrong in this thread so that I can learn something new again

 

[Andre Basson]

Hi All,
VoG II's advice worked - thanks very much.
Had a problem logging back in though, hence the delay. Not sure what I did wrong.
Regards A

 

[FireFighter1005]

Hello and welcome to MrExcel.

This came from John Walkenbach if I remember correctly. Insert this UDF in a standard module:

Function FilterCrit(Rng As Range) As String
    Dim Filter As String
    Filter = "{All}"
    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:
    FilterCrit = Filter
End Function

Then on your worksheet enter the formula

=filtercrit(A3)

where A3 is within the range being filtered.

Thanks, this really helped me getting the user custom criterias from our excel files!