What if statement

[fishcakes]

I want to achieve the following. If "Y" is selected in B3, then the drop down validation in B5 will become the list under Y. If "N" is selected in B3, then the drop down validation or only selection will be "N/A" in B5

 

[awoohaw]

Here is one way . I know there is a way just using the defined name manager, but I could not get it to work:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3		Yes	<<< Data Validation Y/N
4
5			<<<Dependent Data Validation					Y	N	DependentList
6
7								Apple	N/A	Apple
8								Orange		Orange
9								Pear		Pear
10								Grapes		Grapes
11
12
Sheet6

Cell Formulas
Range	Formula
J6:J10	J6	=LET(L,IFS($B$3="Yes",Y,$B$3="No",N,1,""),UNIQUE(IF(L="","",L),FALSE,FALSE))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
N	=Sheet6!$I$6:$I$10	J6
Y	=Sheet6!$H$6:$H$10	J6

Cells with Data Validation
Cell	Allow	Criteria
B3	List	Yes,No
B5	List	=DependentList