Filter Detail Display

We use autofilter extensively so that we can produce generic lists but
allow users to tailor the lists as required. Unfortunately this can
lead to misunderstandings when different filter criteria are applied to
the same list.

How can we display the current criteria while looking at / printing the
data itself ?


---
Message posted from http://www.ExcelForum.com/

John Walkenbach has instructions for showing the criteria that have been
used:

http://j-walk.com/ss/excel/usertips/tip044.htm

steve greenwood wrote:
We use autofilter extensively so that we can produce generic lists but
allow users to tailor the lists as required. Unfortunately this can
lead to misunderstandings when different filter criteria are applied to
the same list.

How can we display the current criteria while looking at / printing the
data itself ?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

That's brilliant , thanks.

I've implemented it in seconds.


---
Message posted from http://www.ExcelForum.com/

Steve,

Use this VB routine and in your filterarea (e.g. above the row autofilters:
let's say A22) take up the formula : = Filtercriteria(A2) ;
=Filtercriteria(B2) etc.
Whenever you than change the "settings" of a filter, the content of it is
given in the row above it.

( I did program this my self, but I found it an excellent tool. There is
however one minor "bug" in it : If you "clean" the filter via the menu :
Data / Filter / Show All the line containing this function isn't cleared as
well. F9 however copes with that).


Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Application.Volatile
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

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"steve greenwood" wrote in
message ...
We use autofilter extensively so that we can produce generic lists but
allow users to tailor the lists as required. Unfortunately this can
lead to misunderstandings when different filter criteria are applied to
the same list.

How can we display the current criteria while looking at / printing the
data itself ?


---
Message posted from http://www.ExcelForum.com/

Where I said I did program it myself I meant to say : I dit NOT program it
myself. !!

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"A.W.J. Ales" wrote in message
...
Steve,

Use this VB routine and in your filterarea (e.g. above the row
autofilters:
let's say A22) take up the formula : = Filtercriteria(A2) ;
=Filtercriteria(B2) etc.
Whenever you than change the "settings" of a filter, the content of it is
given in the row above it.

( I did program this my self, but I found it an excellent tool. There is
however one minor "bug" in it : If you "clean" the filter via the menu :
Data / Filter / Show All the line containing this function isn't cleared
as
well. F9 however copes with that).


Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Application.Volatile
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

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"steve greenwood" wrote in
message ...
We use autofilter extensively so that we can produce generic lists but
allow users to tailor the lists as required. Unfortunately this can
lead to misunderstandings when different filter criteria are applied to
the same list.

How can we display the current criteria while looking at / printing the
data itself ?


---
Message posted from http://www.ExcelForum.com/