Hi All
I need to show the value of the filter selection choosen from the drop down list (certerial) in a cell at the end of a column being filtered.
Is this achieveable in excel 2003
Andy
Hi All
I need to show the value of the filter selection choosen from the drop down list (certerial) in a cell at the end of a column being filtered.
Is this achieveable in excel 2003
Andy
Re: Show filter value in a cell
came up with this which will place the filter critieria in a comment at the top of each filtered range. You can change if you want it in any particular cell.
Sub placefiltercritincomment()
Dim w As Worksheet
Dim f As Integer
Dim filterset As String
Dim currentFiltRange As String
Dim myrange As Range
Set w = ActiveSheet
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
For f = 1 To .Count
filterset = ""
With .Item(f)
If .On Then
filterset = filterset & .Criteria1
If .Operator Then
filterset = filterset & .Operator
filterset = filterset & .Criteria2
End If
End If
End With
Set myrange = w.AutoFilter.Range(f)
On Error Resume Next
myrange.Comment.Delete
On Error GoTo 0
If filterset <> "" Then
myrange.AddComment filterset
End If
Next f
End With
End With
End Sub
Display More
trouble is you'll want to trigger this from an autofilter event, as as these do not directly exist then I wonder how you would prefer to trigger the code?
Re: Show filter value in a cell
Trigeering is a problem as u say, when the filter is set would be ideal.
Iam trying to a static value of filter choosen to use in a vlookup of saleman initials which i filter on to proper names. I did it on the month which is either 1 2 3 etc in acolumn by placing a subtotal that averages so when i filter on say 3 the answer is always 3 no matter how many enteries in the column Then use result in vlookup to return proper month March.
Any other way Vlookup wont work on filter range
Andy
Re: Show filter value in a cell
andy post a sample workbook with a clearer explantion in it and will take a look.
Re: Show filter value in a cell
column P to R for lookup N8 shows month filter value selected and this is displayed as proper month in C5.
What i need is the proper salesman name in I5 when filter is selected for salesman.
with month filter and sales filter selected it produces monthly sales report by salesman in columns B to J
Re: Show filter value in a cell
Hi All
See file attached Sales Results
I can achieve the result of putting the salesman name in cell J2 by assigning salesman initials a number as in column B and an average formula in B68 via the lookup tables, the same method is used to insert the month in D2 when a filter is selected on columns L and M.
What i need really is the filtered value of column L (Salesman initials) placed in L68 when the filter is selected.
Hope this explains the problem
Andy
Re: Show filter value in a cell
Badger,
I came across this thread and had a question on this topic, wondering if you could help. I am trying view in a cell what being filtered. I have the following code that is working if I filter for two items, if I filter for anymore than 2 items in one criteria it does not work. Any idea how I would be able to tweak this code to get it to do what I want? For example if I have all the cities in NY and I want to filter by 3 cities, I want it to say "City 1 or City 2 or City 3"
Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
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
Display More
Re: Show filter value in a cell
AfricanJustin,
Glad to have you here at Ozgrid.
Do not post your question in thread started by others - start a new thread for your question and link back to this thread if you want.
Please learn to use code tags - they are required everytime you post VBA code.
[COLOR="navy"]How to use code tags[/COLOR]
[noparse]
[/noparse]
Or, just highlight all of the code and press the [COLOR="#FF0000"]#[/COLOR] button to add the code tags.
BTW: did you notice this thread is SIX YEARS OLD?
Don’t have an account yet? Register yourself now and be a part of our community!