Excel Datavalidation list if record satisfies the condition

Question

Tasks   | InScope
--------------
T1      |  Yes
T2      |  No 
T3      |  No 
T4      |  Yes

I have a table of above type. Now i want to generate a data validation list of Column "Task" if value of "In Scope" is "Yes".

I tried with using index, but it gives me only single value.

=INDEX($A1:$A100,MATCH(TRUE,INDEX($B1:$B100="Yes",0),0))

Answer 1

If you want it to be dynamic then you will need a helper column.

In the first cell put this formula and copy down the length of the full list:

=INDEX(H:H,AGGREGATE(15,6,ROW($I$2:$I$5)/($I$2:$I$5="Yes"),ROW(1:1)))

Then add a named range with the following formula:

=Sheet4!$K$2:INDEX(Sheet4!$K:$K,MATCH("zzz",Sheet4!$K:$K))

Note: change the sheet and column references to the ones in which your helper column is found. Make sure the references are absolute.

I called mine list

Then in the cell you want you can use =list as the data validation: