Dependent dropdown validation - dependent data is string

**jnewby** · 02-12-2014, 07:19 PM

Hello,

I have created named tables, named ranges and set up data validaton.
Table 1 has two columns (example data to illustrate)
COLOR TextString
Blue Blue is the color of the sky
Blue Blue is the color of the ocean
Red Stop signs are red
Red Blood is red
Yellow They call her mellow yellow

Table 2 simply is a list of the COLORs (Blue, Red, Yellow)

Is there syntax or a method that will display the TextString of the dependent dropdown for selection based on the color selected?
If user selects "Red" from dropdown, then second dropdown displays as a list with two selections of "Stop signs are red" and "Blood is red".

I am using OFFSET with MATCH functions. Appears to work with a single text entity.

Thanks!

Would this require VBA?

**Tony Valko** · 02-12-2014, 07:50 PM

Try this...

Data Range

	A	B	C	D	E
1	-----	-----	-----	-----	-----
2	Blue	Blue is the color of the sky		Red	Stop signs are red
3	Blue	Blue is the color of the ocean
4	Red	Stop signs are red
5	Red	Blood is red
6	Yellow	They call her mellow yellow

D2 = drop down with selections of Blue, Red and Yellow

E2 = drop down based on D2. As the source for this drop down use:

=OFFSET(B2,MATCH(D2,A2:A6,0)-1,,COUNTIF(A2:A6,D2))

However, if you make a different selection in D2 the dependent cell will still be whatever was previously selected. A lot of folks overlook this behavior!