Populating a list based on two variables, excluding entries which are blank

Hello all,

I have a problem which a few hours of googling and playing with formulas has not seemed to have turned up any results.
Although the details are in the attached spreadsheet I shall explain anyway.

I am attempting to populate a list (on the first tab) based on two variables.
One being the type - Expressed by columns of data in the second tab with a data-validated drop down menu on the first tab.
The other being that the word "Yes" appears in the row matching the type.
With all values not meeting these criteria (ie. blank spaces) excluded from the populated list.

A simple example would be as follows.

Name..........Standard..........Standard +..........Art

The A...............Yes................Yes......................
Big B.....................................Yes.................Yes
Mr C.................Yes......................................Yes

When "Standard" is selected from the dropdown menu on the first tab, the list populates as so...
The A
Mr C

When "Standard +" is selected from the dropdown menu on the first tab, the list populates as so...
The A
Big B

When "Art" is selected from the dropdown menu on the first tab, the list populates as so...
Big B
Mr C

If at all possible I would like to avoid VBA and work it out with formulas.


Here is the example sheet.
NCL Example.xlsx


If there is anything that needs clarification then please let me know.

Thank you in advance,


Perth Excel

Hello there,

Attached is a macro version. I prefer to use macros as they are able to function correctly even when your lists grow and categories are added.

Thank tigeravatar, I had tried your workbook and I guess I just added it below the last Category instead of inserting it and it didn't work. But it does if you insert it.

Thanks again! Good to know!

@ ChemistB - Thanks for the quick response this is just what I was looking for!
@ tigeravatar - I had tried named ranges (and had them as named tables as well with the ranges pointing to these as to make them dynamic named lists) but i just couldn't figure out the forumla. The spaces were helper columns I was using from a different project but I realised I couldn't use them in this one after i tried it. As for the merged cells in the first tab, That was merely for User Friendliness (and because I planned on adding more features later based on this methodology).
@ rvasquez - I was trying to avoid VBA / Macro's as I don't have much experience with them, and I find working out what is going on in formulas easier if I come against a problem but your solves the problem as well. I eventually want to learn how to programm VBA / Macros, any idea's on quick learning resources?

Thanks everyone for your hard work (or easy if you found it simple). + Reputations all round and SOLVED (3 different ways).

@ Perth Excel

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

@Cutter - Apologies, I had tried to unsuccessfully many times but under the thread thools where I usually have "Mark as Solved" it read "Mark as Unsolved". However many times I refreshed the page, deleted browser cookies etc. I could not get it to change. Not sure if this is possibly a bug or my computer having a bad day. Nevertheless I have managed to do this now (after another three rounds of the same "Unsolved" instead problem as yesterday).