Data Validation Dynamic List

[Koza84]

Hi,

I am looking for solution for my drop down data validation menu, it would be in column B in sample data ( "Units"). What I am trying to do is to have dynamic named range to choose available unit types based on if data has been filled in in columns E to I

For example in case of "Apple", dynamic named range would be Grams,Cup,Whole, because there columns are filled in. For Banana it Grams only. For Strawberry it would be Grams,Cup.

So If I choose food in column A then in column B would have that dynamic named range for unit types available based on type of food in column A. Drop down data validation would be also in few more columns.

Is there any way to achieve this?

Food	Units			Grams	Tsp	Tbs	Cup	Whole
Apple			Apple	1			100	120
Banana			Banana	1
Apple			Strawberry	1			80

 

[jasonb75]

Until data validation gets an upgrade that works with array formulas, you will need to set it up as shown in the link below with a list of valid selections for each food type.

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

 

[Koza84]

Until data validation gets an upgrade that works with array formulas, you will need to set it up as shown in the link below with a list of valid selections for each food type.

Easy Steps Excel Dependent Drop Down List Data Validation

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, Region/City. Videos, written steps, get free Excel file

www.contextures.com

Thanks. This method will not allow spaces in the name of my food. Is there any work around this?

 

[jasonb75]

You can either create the named ranges without spaces, or use underscores instead, then use =INDIRECT(SUBSTITUTE( to change the text in the cell as needed.

 

[Koza84]

Will try thanks!