Apply Filter on Data Validation List based on row value

[usmanmunir01]

HI I have two Columns

[TABLE="width: 500"]
<tbody>[TR]
[TD]Data[/TD]
[TD]Products[/TD]
[/TR]
[TR]
[TD]ON[/TD]
[TD]Product 1[/TD]
[/TR]
[TR]
[TD]OFF[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD]ON[/TD]
[TD]Product 3[/TD]
[/TR]
</tbody>[/TABLE]

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)