I think this one should be easy, but for some reason I can't figure it out. When you autofilter a column, is there a way to refer to the selection criteria elsewhere on the worksheet? For example, let's say I have an autofiltered column with a list of states, and the user selects "Minnesota" as the filter criteria. Is there a way to make another cell now change to "Minnesota"? If I use relative or absolute referencing, the value stays the same regardless of the filter. Any ideas?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Referencing Auto-Filtered Criteria
- Thread starter devingm
- Start date
TonyJollans
Programmer
Hi devingm,
You could do it with code but there are a lot of complications because the autofilter has more capacity than just the simple example you quote. What would you want in your cell if the user filtered on, say, "Minnesota" or "any state beginning with N"?
Enjoy,
Tony
--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[
You could do it with code but there are a lot of complications because the autofilter has more capacity than just the simple example you quote. What would you want in your cell if the user filtered on, say, "Minnesota" or "any state beginning with N"?
Enjoy,
Tony
--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[
- Thread starter
- #3
Then I would want it to say "any state beginning with N". I can do this in a macro no problem, but I need it to change while the user is working on it. A more common situation in this spreadsheet would be the user filtering all non-blanks, so I'd want to be able to display that, which I'm not sure how to. Specifically, it's for the page heading so that they know by looking at the printouts what was autofiltered. Thanks for your help.
TonyJollans
Programmer
Hi devingm,
If you're happy interpreting the Filter Criteria in code then that is the way (I think the only way) to go. Write it as a UDF and put Application.Volatile as the first line and you should get real time updating of it.
Non-blanks is held as a criteria1 of [blue]<>[/blue]; you could translate it into English. A harder one for my money is "Top 10".
Enjoy,
Tony
--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[
If you're happy interpreting the Filter Criteria in code then that is the way (I think the only way) to go. Write it as a UDF and put Application.Volatile as the first line and you should get real time updating of it.
Non-blanks is held as a criteria1 of [blue]<>[/blue]; you could translate it into English. A harder one for my money is "Top 10".
Enjoy,
Tony
--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question