a formula to pick up a column's filter condition

[Roman]

I need a formula that picks up what filter condition is used on a
column of values.

For example:


Column A Column B


***************************** =filter
condition
People: (heading w/ filter)
*****************************
John
John
Mary
Ben
Duke
Mary


If I filter Column A by John the formula should return "John". If I
select all names in the filter then the formula should return "None".
If I select more than one name, the formula should return "None" as
well. I want the formula to recognize the filter condition only when
the user filters the column by one item at a time.


Please ask me if you need more details.


Thanks,
Roman

 

[Dave Peterson]

<saved from a previous post>

I saved this from a Tom Ogilvy post:
====================================

http://j-walk.com/ss/excel/usertips/tip044.htm
or
http://spreadsheetpage.com/index.php/tip/displaying_autofilter_criteria/

to get it to refresh:

=FilterCriteria(B5)&left(Subtotal(9,B5:B200),0)

this is one I wrote back in 2000

Here is a user defined function that will display the criteria in a cell:

Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function

=ShowFilter(B5)&left(Subtotal(9,B5:B200),0)

would show the filter for column 2

I usually put these functions in cells above the filter

==============
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
====================

You may want to put the formula above the cell with the autofilter arrow.


===========
If you want to use this with lots of workbooks, then you could put it in your
personal.xls workbook. You'd use it like this:

=personal.xlsm!ShowFilter(B5)&left(Subtotal(9,B5:B200),0)

If you want to share the single workbook with others, then you'd want to put it
into the workbook.

But if you want to use it with lots of workbooks and share with others, then I'd
suggest that you put it in a separate workbook (PhilsUtils.xlsm) and share that
with others.

Tell them to save your workbook in a folder on their C: drive. And everyone
should use the same name:

C:\xlUtils\philsutils.xlsm

(You could also distribute an addin (.xlam) and have them store it in the same
location, but give them instructions on how to install the addin.)

 

[Roman]

<saved from a previous post>

I saved this from a Tom Ogilvy post:
====================================

http://j-walk.com/ss/excel/usertips/tip044.htm
orhttp://spreadsheetpage.com/index.php/tip/displaying_autofilter_criteria/

to get it to refresh:

=FilterCriteria(B5)&left(Subtotal(9,B5:B200),0)

this is one I wrote back in 2000

Here is a user defined function that will display the criteria in a cell:

Public Function ShowFilter(rng As Range)
Dim filt AsFilter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
  ShowFilter = "No ActiveFilter"
  Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
  ShowFilter = CVErr(xlErrRef)
Else
 lngOff = rng.Column - frng.Columns(1).Column + 1
 If Not sh.AutoFilter.Filters(lngOff).On Then
   ShowFilter = "No Conditions"
 Else
   Set filt = sh.AutoFilter.Filters(lngOff)
   On Error Resume Next
    sCrit1 = filt.Criteria1
    sCrit2 = filt.Criteria2
    lngOp = filt.Operator
    If lngOp = xlAnd Then
     sop = " And "
    ElseIf lngOp = xlOr Then
     sop = " or "
    Else
     sop = ""
    End If
   ShowFilter = sCrit1 & sop & sCrit2
  End If
End If
End Function

=ShowFilter(B5)&left(Subtotal(9,B5:B200),0)

would show thefilterfor column 2

I usually put these functions in cells above thefilter

==============
If you're new to macros, you may want to read David McRitchie's intro at:http://www.mvps.org/dmcritchie/excel/getstarted.htm
====================

You may want to put the formula above the cell with the autofilter arrow.

===========
If you want to use this with lots of workbooks, then you could put it in your
personal.xls workbook.  You'd use it like this:

=personal.xlsm!ShowFilter(B5)&left(Subtotal(9,B5:B200),0)

If you want to share the single workbook with others, then you'd want to put it
into the workbook.

But if you want to use it with lots of workbooks and share with others, then I'd
suggest that you put it in a separate workbook (PhilsUtils.xlsm) and share that
with others.

Tell them to save your workbook in a folder on their C: drive.  And everyone
should use the same name:

C:\xlUtils\philsutils.xlsm

(You could also distribute an addin (.xlam) and have them store it in thesame
location, but give them instructions on how to install the addin.)












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave. This is exactly what I needed.