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