Excel should have a drop down box that shows all the filters on

[dasmjp]

We use large Excel files with filters. I waste time scrolling across columns
to make sure what filters are still on or off. Is there a feature in Excel
where you can
look to see what columns have filters on them?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Elkar]

Unfortunately, the only built-in feature Excel offers is a nearly
imperceptible color change of the filter arrow from black to dark blue.
Unless you've got great eye-sight, this probably isn't very helpful.

Here's a User-Defined Function that I got from this forum that I use on most
of my sheets that have lots of AutoFilters. Create a module for your
workbook, then copy this code into it.


Function FilterCriteria(Rng As Range) As String
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:
FilterCriteria = Filter
End Function


I then insert a new row above my Filter heading and use this formula:

=IF(filtercriteria(A3)="","(none)",filtercriteria(A3))

HTH,
Elkar

 

[Nick Hodge]

No, apart from the blue arrows in the filter dropdowns.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)