# Data Validation List Based on Meeting Criteria in Adjacent Cell



## mharper90 (Jan 5, 2023)

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 BARCARDSTOCK COLORAnalytical Instrument ChecksBlueAux Boiler SteamBlueBLANKBlue


----------



## Skybluekid (Jan 5, 2023)

This is a bit "Heath Robinson", but it does what you require
Book1BCDEFGHI2SEARCH BARCARDSTOCK COLOR3Analytical Instrument ChecksBlueAnalytical Instrument ChecksAux Boiler Steam4Aux Boiler SteamBlueAux Boiler Steam5Blue6Analytical Instrument ChecksGreen7Aux Boiler SteamGreen8Green9Sheet1Cell FormulasRangeFormulaE3:E4E3=FILTER(Sheet1!$B$3:$B$8,((Sheet1!$B$3:$B$8<>"")*(Sheet1!$C$3:$C$8="Blue")))Dynamic array formulas.Cells with Data ValidationCellAllowCriteriaH3List=$E$3#

I am sure there is a better way


----------



## Icy Pilgrim (Jan 5, 2023)

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 (Jan 5, 2023)

Skybluekid said:


> This is a bit "Heath Robinson", but it does what you require
> Book1BCDEFGHI2SEARCH BARCARDSTOCK COLOR3Analytical Instrument ChecksBlueAnalytical Instrument ChecksAux Boiler Steam4Aux Boiler SteamBlueAux Boiler Steam5Blue6Analytical Instrument ChecksGreen7Aux Boiler SteamGreen8Green9Sheet1Cell FormulasRangeFormulaE3:E4E3=FILTER(Sheet1!$B$3:$B$8,((Sheet1!$B$3:$B$8<>"")*(Sheet1!$C$3:$C$8="Blue")))Dynamic array formulas.Cells with Data ValidationCellAllowCriteriaH3List=$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 (Jan 5, 2023)

You are welcome @Icy Pilgrim


----------

