Input filtered item in a separate cell

  • Thread starter Re: Inserting an option button in Word
  • Start date
R

Re: Inserting an option button in Word

Anyone know how I can filter a list by a variable in a list and have cell A2
always show the latest variable the list is filtered by. The list would start
on say Row 4 so e.g Heading is Product, this column filtered to show the word
Health. I want this to automatically also appear in Cell A2. using Excel 2003.

Thanks in advance.
 
B

Bernie Deitrick

From Tom Ogilvy:

*******************************
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.
******************************************************
Note that the Subtotal function is used to re-calc the cell when the filter is changed - that won't
ahppen if you just us the ShowFilter function.

HTH,
Bernie
MS Excel MVP
 
R

Re: Inserting an option button in Word

Thanks Bernie,

I'll try it now.

Bernie Deitrick said:
From Tom Ogilvy:

*******************************
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.
******************************************************
Note that the Subtotal function is used to re-calc the cell when the filter is changed - that won't
ahppen if you just us the ShowFilter function.

HTH,
Bernie
MS Excel MVP
 
Joined
Aug 15, 2017
Messages
1
Reaction score
0
Hello,
tks a lot for this code Bernie.
i tried it and it works like a charme.
i have issue only if i filter a column with date because result is a white cell.
i tried also with format command on the sCrit2 but any result.
any suggestion?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top