Dependant drop down validation with illegal characters

**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