Trying to make multiple dependent drop down list with dynamic ranges

**Gwen@YouWantWhat?** · 09-28-2012, 01:10 PM

I want to set up two drop-down lists. The selection you make determines what you get in the second drop-down. The trouble is, I want the ranges to be dynamic, so I can add more materials to the lists without having to rewrite the code. I have successfully done it with a static range.

This website has exactly the example I'm looking for at the very bottom of the page.
http://www.contextures.com/xlDataVal02.html
The trouble is, I haven't been able to make it work. I even tried recreating her example, and it wouldn't work. It says that it is referencing an error, and the drop down doesn't show any options even when the first column is filled in correctly.

**Søren Larsen** · 09-28-2012, 01:22 PM

There could be a number of reasons why it isn't working for you. Perhaps you could upload your workbook, or a sample of it?

**Gwen@YouWantWhat?** · 09-28-2012, 03:51 PM

sample.xls

Attached is a simplified sample of my document. The only things I changed from Contexture's example is not including the SUBSTITUTE function, as none of my categories have multiple words. (I would eventually like to make the sub-categories not include the column title, but that can be fixed after the real problem is solved.)

**ChemistB** · 09-28-2012, 04:29 PM

Your data validation formula in C2 is referencing cell B2. It should be referencing B5 (twice)
Does that help?

**Gwen@YouWantWhat?** · 09-28-2012, 05:09 PM

That wasn't actually the problem in the original document, but it did make me find it! I had been using a dynamic OFFSET list for each of my column lists, and that was what was throwing things off. Thank you!