Dependant drop down validation with illegal characters

**mewingkitty** · 12-01-2008, 06:38 PM

The title pretty much says it all.

I followed through
http://www.contextures.com/xlDataVal13.html
to try to figure this out myself. I don't follow... to put it bluntly.

Does anyone have an example of dependant drop boxes with illegal characters in them? Even just a teensy one... If I have an example I can manipulate I can actually see what does what... The several-paragraph-long explanation with descriptions of what's being referenced by what and where and why - isn't helping me any.

**mewingkitty** · 12-01-2008, 07:18 PM

I think I kinda get it...
Maybe.

**mewingkitty** · 12-01-2008, 08:08 PM

k so here's what I'm workin on anyways, nothing confidential in there thus far so no harm in posting it. See cells b2:e2 for what I'm trying for. I need a system that will narrow down choices through options in order to select a single product from a large number of variables.

I've obviously gone about this the wrong way... any suggestions on where to start over?

**teylyn** · 12-01-2008, 11:42 PM

Hi,

the article you are referring to uses a technique that only works with two levels of dependencies, in the example it's Region and customer. You have four levels, so you need to take a different approach.

I've demoed it here http://www.excelforum.com/excel-gene...y-a-range.html

You'll still need to use the lookup trick for the values with illegal characters as described here http://www.contextures.com/xlDataVal02.html

Hope that helps

Teylyn

**mewingkitty** · 12-02-2008, 10:49 AM

Could you elaborate a bit on...

Range Name "secondList" is dynamically assigned the name in C3
Range Name "thirdList" is dynamically assigned the name in D3

?
I can see that it's dynamically assigned, but I don't have a clue how... What I mean to say is - How does Excel know which list is defined as "SecondList". I see it in the data entry as "=Secondlist", but I don't see anything else defining what "Secondlist" is.

Maybe it is just that I can not brain today, because I have the dumb.
http://icanhascheezburger.files.word...rain-today.jpg

Anyways,
This does look like it'll work for me, I just need to have a full understanding of it on account of the scale on which i'll be attempting to use it.

Thanks in advance!

mew!

**teylyn** · 12-02-2008, 04:36 PM

OK,

trying to explain the example file in the post I linked to above.

We have cascading validation on three levels.

The first validation takes the list values from a range called Manufacturers. This range name is a fixed range. No big deal. Say you select the Manufacturer called "Bravo".

The second validation now needs to take its values from the range called "Bravo", but the range listed in the validation setup is called "secondList".

secondList is a range name as well, but where it actually points to gets changed dynamically. If you go Insert - Name - Define and scroll down to the defined name "secondList" you see that it refers to

=INDIRECT(Sheet1!$C$3)

Whazzat?

Well, I want "secondList" to refer to whatever is displayed in cell C3, but I need the value of C3 interpreted as a range name.

INDIRECT() returns the reference specified by a text string, so if C3 is "Bravo", then I get "Bravo" back. Since the Indirect function defines the range for "secondList", this means that "secondList" now equals "Bravo".

secondList is my list for the data validation in D3, and so "Bravo" is now the range for the validation list.

I could actually use the Indirect function in the data validation setup, but since it is so cryptic it can be easy to get lost. It's easier to figure out the logic of the flow with "secondList", "thirdList" etc. than with "=INDIRECT(Sheet1!$C$3)" and "=INDIRECT(Sheet1!$D$3)".

"secondList" is a dynamic range. Its content is assigned with a formula. These range names don't appear in the Goto dialog or when you press F5. But if you know they're there, you can check them with Insert - Name - Define, select the name, place the cursor in the "Refers to:" box and the range will be highlighted.

Hope that takes away some of the confusion.

cheers

Teylyn