return filtered criteria to a cell

  • Thread starter Thread starter eggman
  • Start date Start date
E

eggman

Is there a way to return the value an autofilter list is filtered by to a
cell at the top to use as a heading?

i.e. If I use autofilter to filter a parts list to show only the "Promo"
item class, could I have cell B1 return "Promo"

the data is in A5:H6502

And then when I filter to only show the "209A" item class, B1 would return
209A

TIA
 
eggman,

From a previous post:

Public Function ShowFilter(rng As Range)
'UDF that displays the filter criteria.
'posted by Tom Ogilvy 1/17/02
'To make it respond to a filter change, tie it to the subtotal command.
'=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)
'So the above would show the criteria for column B

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

HTH,
Bernie
MS Excel MVP
 
Jason Morin said:
Yes. See:

http://tinyurl.com/7yz6x

Change the ranges to meet your needs.
Click to expand...

It only works for the first row, when I change the filter, I get #VALUE!
Here is my formula, Parts_Page is a dynamic range using the OFFSET(), but I
also tested it by using the actual cell range and got the same result.

=INDEX(Parts_Page,MAX(ROW(Parts_Page)*SUBTOTAL(3,OFFSET(Parts_Page,ROW(Parts
_Page)-MIN(ROW(Parts_Page)),,1))))

Am I doing something wrong?

 
It's an array formula, so after inserting the formula
into the cell and anytime you edit that cell, hold down
the <ctrl> and <shift> keys and press <enter>. Excel will
place {} around the formula to indicate that it's an
array formula.

Jason
 
Jason Morin said:
It's an array formula, so after inserting the formula
into the cell and anytime you edit that cell, hold down
the <ctrl> and <shift> keys and press <enter>. Excel will
place {} around the formula to indicate that it's an
array formula.
Click to expand...

I got it to work.. I am trying to follow the logic of each individual
formula, but am still confused. It initially returned the next criteria
option below the one that I selected. To combat this, I played with it and
had to add a -2 to the end of the formula. Does that make sense? Well at
any rate it works, and here it is:

{=INDEX(Parts_Page,MAX(ROW(Parts_Page)*SUBTOTAL(3,OFFSET(Parts_Page,ROW(Part
s_Page)-MIN(ROW(Parts_Page)),,1)))-2)}

Also... thanks to Bernie for Tom's UDF. That works great as well, but then
users will have to say yes to enable macros which often confuses my
co-workers.

Thanks again Jason and Bernie (Tom)
 
Back
Top