How to only display questions by a filter

It's not entirely clear (to me) what you want, but try:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$11)/(INDEX($F$2:$H$11,,MATCH($E$1,$F$1:$H$1,0))="Yes"),ROWS(D$2:D2))),"")

in combination with the drop-down box in E1.

See file.

Are you still using Excel 2016?

Hi Ali,

Unfortunately yes Excel 2016 as we have over 3000 users so the business cost to upgrade restricts when newer versions are rolled out.. Additionally, this questionnaire is issued out to third parties, and we have no idea what version they are using, so I have to ensure its compatible.

Hi Glenn,

Think my objective isn't clear maybe..

I have a questionnaire with hundreds of questions, but not all questions are applicable to be asked. SO before the questionnaire is issued I need the user to make a selection of either High, Medium or Low.

Making this selection will dictate which questions are displayed..

So if High is selected - then it would display all questions.
If Medium is selected - only medium & low rated questions will be displayed.
If low is selected - only low rated questions will be displayed.

In the example sheet i provided, you can see columns F, G & H dictate which questions are applicable to the question rating of High, Medium or Low. But rather than having to filter on those 3 columns, I wanted to find a way to have one filter with 3 options if that makes sense. Columns F, G & H can remain, so just need Column D to be populated with formula to show High / Medium / Low based on columns F, G & H

Multiple columns can be added for formulas if needed, as they will be hidden and locked anyway..

Thanks, Grant

and we have no idea what version they are using,

And how old could their systems be? How far back does compatibility need to go? Which version? This is a critical bit of information.

I can't follow that. Please populate your sheet with EXACTLY what you want to see when, say, medium is selected (Where is L/M/H to be selected??).

Too vague, so far.

PL see file.In E2

Formula:

Please Login or Register  to view this content.

Data validation done to E1.

Try in D2

=INDEX($F$1:$H$1,MATCH("Yes",F2:H2,0))

I asked you to show expected results - you didn't. There is no Dropdown in D1. There is now.

One final guess (in the absence of expected answers clearly shown):

=IF(INDEX($F$2:$H$11,,MATCH($D$1,$F$1:$H$1,0))="Yes",D1,"")

Hi Glenn,

Appears to be what I needed, will go apply to mine and see if right

When you say one step, are you trying to get rid of one of those columns?

Here's one possibility. In L3 copied down:

=TEXTJOIN(", ",,FILTER($H$2:$J$2,H3:J3="Yes"))

Column L can be hidden.

In D3 copied down:

=L3

Now you can filter on a combination. Maybe not as pretty, but it's a one-stop shop for the user.

What do you mean?

I am able to select Low, Medium, High, for example, then go back to the filter and still see the other two available to select.

Oh, poo!

I did not realise you had Excel 2016 - sorry. Give me a mo!

In L3 copied down:

=IF(A3="","",IF(G3="N/A","",G$2&", ")&IF(H3="N/A","",H$2&", ")&I$2)

Are we there yet?

I cannot think of a way, but that doesn't mean there isn't one - if there is, it would probably involve VBA.

Why would it NOT be more user friendly than the two-step version? Wherever you have two choices, if you do things in the wrong order, you end up with frustration. I would put up with the less pretty but INFINITELY simpler option I've offered you.

Anyway, I have given you what you asked for in terms of the way it works. It works, but apparently it's not good enough ...

You're welcome.

I did look again, but I can't find a way to control filters formulaically, so I think it would have to be VBA.

If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.