Dynamic Named Ranges with Data Validation

[Ioweyouone]

To All: Please look at the screen shot below. I'd like D2 to be a drop down of only the States with Blue next to their name. This list would change depending on the color selected in C2. The user selects Cell C2 first. It is also a drop down with only two choices, Blue or Red. Thanks.

 

[kevin9999]

Something like this? Only showing as far as Maine as per your image.

Book1
	A	B	C	D
1	COLOR	STATE	SELECT COLOR	SELECT STATE
2	Blue	Alabama	Blue	Alabama
3	Blue	Alaska		Alaska
4	Red	Arizona		Arkansas
5	Blue	Arkansas		Colorado
6	Red	California		Connecticut
7	Blue	Colorado		Idaho
8	Blue	Connecticut		Iowa
9	Red	Delaware		Kansas
10	Red	Florida		Louisiana
11	Red	Georgia		Maine
12	Red	Hawaii
13	Blue	Idaho
14	Red	Illinois
15	Red	Indiana
16	Blue	Iowa
17	Blue	Kansas
18	Red	Kentucky
19	Blue	Louisiana
20	Blue	Maine
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=FILTER(B2:B51,A2:A51=C2,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	Blue,Red

 

[Ioweyouone]

Kevin9999, thanks for the reply but I need to clarify the objective.
I need cell D2 to be a Data Validation drop down list. Within that list, the user would only be able to select the States with the Blue next to their name. Can we use that FILTER function somehow in the validation criteria source?

 

[kevin9999]

Understood. If you're OK with a helper column (say column E) then will the following work for you? (You can always hide column E if desired)

Book1
	A	B	C	D	E
1	COLOR	STATE	SELECT COLOR	SELECT STATE
2	Blue	Alabama	Blue		Alabama
3	Blue	Alaska			Alaska
4	Red	Arizona			Arkansas
5	Blue	Arkansas			Colorado
6	Red	California			Connecticut
7	Blue	Colorado			Idaho
8	Blue	Connecticut			Iowa
9	Red	Delaware			Kansas
10	Red	Florida			Louisiana
11	Red	Georgia			Maine
12	Red	Hawaii
13	Blue	Idaho
14	Red	Illinois
15	Red	Indiana
16	Blue	Iowa
17	Blue	Kansas
18	Red	Kentucky
19	Blue	Louisiana
20	Blue	Maine
21
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=FILTER(B2:B51,A2:A51=C2,"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	Blue,Red
D2	List	=E2#

 

[Ioweyouone]

I like it. I'll start trying to work it into my sheet.
If I need to add more data past row 51, will the FILTER function automatically expand down the column?
Also, the validation list criteria for D2 is =E2#. What is the purpose of the # at the end?

 

[kevin9999]

If I need to add more data past row 51, will the FILTER function automatically expand down the column?

No, instead change the formula to include the entire columns like:

=FILTER(B:B,A:A=C2,"")

Also, the validation list criteria for D2 is =E2#. What is the purpose of the # at the end?

It allows for a Spill, in other words, it doesn't matter how many cells are filled below E2 - use of the hash # will pick them up.

 

[Ioweyouone]

Thank you Kevin9999.

 

[kevin9999]

Happy to help, and thanks for the feedback