I am trying to create a formula, say in cell B1 that would return the same
info that is returned from my filter. For example if i filter my data on
colors and I return a 'red' result, I would like cell B2 to read 'Colors -
Red'

See this:

http://tinyurl.com/2885hu

--
Biff
Microsoft Excel MVP


"RBG" wrote in message
...
I am trying to create a formula, say in cell B1 that would return the same
info that is returned from my filter. For example if i filter my data on
colors and I return a 'red' result, I would like cell B2 to read 'Colors -
Red'

Courtesy of Tom Ogilvy

You use it like so

=L1&":"&showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+32)

The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed

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



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"RBG" wrote in message
...
I am trying to create a formula, say in cell B1 that would return the same
info that is returned from my filter. For example if i filter my data on
colors and I return a 'red' result, I would like cell B2 to read 'Colors -
Red'