This may turn out to be one of those easy questions that makes me smack my head, but I’ve been digging through Excel’s help file for a while now and have come up dry. Anyway:
Is there a way to return the value of what a user selects when he/she selects a value in a filtered list?
For example: Let’s say I have a list of names in a column, and the user has filtered the list on the name “Joe.” Is there a way to reference the value “Joe” so I can use it in a formula?
Or, if you need to assign a value to each possible name I’d set up a lookup table with name in column A and value in column B. Give the table a range name. Add a column to your list of names with the formula
=VLOOKUP(A1,Lookuptable,2,false)
(Find Cell A1 in the lookup table, return the value in the 2nd column of the table, only if A1 matches exactly what’s in the first column of the table)
Essentially what I want to do is to return the criteria that a user selected when they filtered a list. So in my list of names example: Let’s say the list is 1,000 rows long and is comprised of 50 different names. The list already has the data filter applied (so that the little drop down arrow is in the header row of the column). The user will click the drop down arrow to filter on the desired name, but I don’t know which name he/she will be selecting, so I can’t test for it in advance with a lookup formula. The users will also be adding to the list of names, so I can’t limit myself with a static list of values to test against.
Excel knows that the user selected “Joe” as the filter criteria, so I assume that data is stored somewhere in the dank inner recesses of Excel. What I want to be able to do is pull the value that was seleted from the filter list (“Joe”) and use it another formula.
I could probably find a way around it using VBA, but I’d rather not spend the time if there is already a built-in way to find the selected value.
Don’t feel bad AllShookDown. I tried the same thing myself and realised why that wouldn’t work.
keno – You put me on the right track, so thanks. I tweaked your example from the VBA help file and got it to work as a procedure in my sheet. Mine looks like this:
Sub filtercrit()
With ActiveSheet
If .AutoFilterMode Then
With .AutoFilter.Filters(1)
If .On Then c1 = .Criteria1
End With
End If
Cells(1, 1).Value = c1
End With
End Sub
I think it will work better for me if I set it up as a function instead of a sub, so I just need a little more tweaking and I’m all set.
Function filtercrit()
With ActiveSheet
If .AutoFilterMode Then
With .AutoFilter.Filters(1)
If .On Then c1 = .Criteria1
End With
else
c1 = “None selected”
End If
filtercrit = c1
End With
End Sub
You might want to check out the Excel-G or Excel-L lists at PEACH.EASE.LSOFT.COM