Apply Filter on Data Validation List based on row value

[usmanmunir01]

HI I have two Columns

Data	Products
ON	Product 1
OFF	Product 2
ON	Product 3

<tbody>
</tbody>

I want to populate a data Validation list which has the values where Data=ON

Please help.

 

[BobUmlas]

In an unused area, say H1, enter this formula via ctrl+shift+enter:
=IFERROR(INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10="on",ROW($A$1:$A$10),""),ROW(A1))),"")
Fill down as far as you need
In I1, ctrl+shift+enter this:
=MATCH(TRUE,H:H="",0)-1
Define a name, like Prods, to be =OFFSET($H$1,0,0,$I$1,1)
(Excel will append the sheet name).
Now use data validation somewhere with a list where the list name is =Prods
(ensure calculation is automatic)