Data Validation List Based on Meeting Criteria in Adjacent Cell

[mharper90]

I am trying to make a data validation list on Sheet1 that references all non-blank items in column A on Sheet2. I have accomplished this pretty easily (although, is there a way to exclude the header without just selecting a long range like "A2:A5000"?).

Now, I want to trim that list to only display those values if they ALSO have the text "Blue" in column B. This will eventually expand to include other colors of cardstock as items are added to the list, but each data validation list will only need to display list items associated with a single color. I will copy, paste, and modify this process to the other data validation cells as needed for different colors.

I have seen many examples of multi-level drop down lists, such as Fruit->Grape, but those examples don't seem to reference an adjacent cell, they seem to reference the title of the list.

SEARCH BAR	CARDSTOCK COLOR
Analytical Instrument Checks	Blue
Aux Boiler Steam	Blue
BLANK	Blue

 

[Skybluekid]

This is a bit "Heath Robinson", but it does what you require

Book1
	B	C	D	E	F	G	H	I
2	SEARCH BAR	CARDSTOCK COLOR
3	Analytical Instrument Checks	Blue		Analytical Instrument Checks			Aux Boiler Steam
4	Aux Boiler Steam	Blue		Aux Boiler Steam
5		Blue
6	Analytical Instrument Checks	Green
7	Aux Boiler Steam	Green
8		Green
9
Sheet1

Cell Formulas
Range	Formula
E3:E4	E3	=FILTER(Sheet1!$B$3:$B$8,((Sheet1!$B$3:$B$8<>"")*(Sheet1!$C$3:$C$8="Blue")))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H3	List	=$E$3#

I am sure there is a better way

 

[Icy Pilgrim]

This is exactly what I've struggled with today. It seems the DV dialog box doesn't recognise dynamic arrays automatically - when selecting the cells I had to add the # symbol myself.

Thanks for the solution Skybluekid

 

[mharper90]

This is a bit "Heath Robinson", but it does what you require

Book1
	B	C	D	E	F	G	H	I
2	SEARCH BAR	CARDSTOCK COLOR
3	Analytical Instrument Checks	Blue		Analytical Instrument Checks			Aux Boiler Steam
4	Aux Boiler Steam	Blue		Aux Boiler Steam
5		Blue
6	Analytical Instrument Checks	Green
7	Aux Boiler Steam	Green
8		Green
9
Sheet1

Cell Formulas
Range	Formula
E3:E4	E3	=FILTER(Sheet1!$B$3:$B$8,((Sheet1!$B$3:$B$8<>"")*(Sheet1!$C$3:$C$8="Blue")))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H3	List	=$E$3#

I am sure there is a better way

Thanks. This is an option I stumbled upon shortly after posting my initial question. It works, but I'd love a way to skip the middle cell and put the array formula right into the data validation cell. Perhaps it's not possible.

 

[Skybluekid]

You are welcome @Icy Pilgrim