Forum Discussion
Excel - How to filter to show subset of questions for selected criteria
I have a set of 500 questions for different interest groups in a spreadsheet. The questions are tagged "Y" if it is applicable to a specific interest group. See table below.
Is there a way to filter these questions such that if I will see questions tagged "Y" for "All Interests", Interest 1, Interest 2 or Interest 8. That is to say if "All Interests" has 10 questions, Interest 1 has 5 questions, Interest 2 has 6 questions and Interest 8 has 9 questions, I should see 10+5+6+9 = 30 questions on the spreadsheet. Is this achievable via filter and if yes, what are the steps and if no, any suggestions how this can be achieved? Any help appreciated?
Thanks in advance.
@
4 Replies
You can use Advanced Filter for this.
Elsewhere on the same sheet, create a range like this:
The headers in the top row should be exactly as in your question set.
Click anywhere in your set of questions.
On the Data tab of the ribbon, in the Sort & Filter group, click Advanced.
Excel should automatically select the entire set in the List range box.
Click in the Criteria range box, then select the range that you created as in the screenshot.
Click OK.
Thanks!! However, I noticed that the filter works if there is "Y" indicated in every column but fail to work if there is say a "blank" or "N" in the appropriate cell in the respective column. Is there something that I am missing or is that how Advanced Filter works? How do I get the filtered questions without having to delete the columns not needed and reapplying the filter? The reason why I am asking this is because the filter is to be used by end-user and I am hoping for a more elegant solution.
Many thanks in advance!!
Sorry, I don't get your question. What exactly do you mean?
