Excel 2007 : Dependent Dropdown (Data Validation)

**mtpr220** · 05-06-2010, 05:17 PM

thanks nbvc for your quick reply, however i should have been a bit more clear. I just figured out how to do the drop down tables with data validation yesterday, which is fairly straight forward, however, i am a bit unclear as to how the line:

=INDIRECT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1&B1&C1,"(",""),")",""),CHAR(176),"")," ","")))

actually works to point to each individual list i want to display. I was successful in testing the simpler cascading dependant drop down menu tutorials with one dependant menu, but i think im lacking an understanding of how these individual functions work when they are combined for this specific case. i have had no previous exposure to INDIRECT, TRIM, or SUBSTITUTE before, so would you be able to walk me through this line a bit more in depth? as of now i dont really understand what to modify from this code to reference the lists im looking for. I renamed each of the 12 resulting lists as you described above "InConduitCopper60C140F" and so on.

overall i get that substitute replaces spaces with underscores to avoid errors, and i assume Char refers to the "degree" symbol as an illegal character. indirect is somewhat of a mystery to me though.

for a1 b1 and c1 i would assume this is in reference to the drop down menus i want to use to determine which list is displayed in the fourth menu, but im having a bit of trouble wrapping my head around howit fgures out which combination of the 3 will display a specific menu in the fourth.

again, i apologize if this is confusing, im a noob.

hold that thought, i think i understand it now, give me 15 minutes... *working*

**mtpr220** · 05-06-2010, 11:23 PM

okay so i spent some time on this after work, and i understand how the code works, so ignore my last post.

char(176) is °
trim removes leading and trailing spaces
substitute just replaces the "(" ")" "°" and " " with ""
indirect references a range

so... if i understand correctly,

make a list for each possible combination of the 3 categories
concatenate each possible combination from the 3 categories as the reference(range name) for each individual list

the code is used to recognize the concatenated combination from the 3 categories and then pulls the appropriate list.

this has worked so far, however. i am now having some trouble with named ranges, i cannot seem to remove the range name from a range i incorrectly named. i was wondering if there is a quick and easy way to highlight the whole set of 12 lists and remove the names to start over. also is there a difference if i changed some of the names from the name box or from the define name tab under formulas?

and one more thing, i would ideally like the lists of available wire type to be dynamic, so if i want to add additional wire types to a specific list, i dont have to change the named range, it will just add automatically. some of the other dependant drop down tables use the offset function to create a dynamic range, how hard would this be to implement?

thanks again for your help.

**NBVC** · 05-07-2010, 07:46 AM

Check out this link to learn how to create dynamic named ranges:

http://www.contextures.com/xlNames01.html#Dynamic

I think the best thing to do is delete the named ranges you don't want one at a time through the Insert|Name|Define dialog. It's the safest bet that you will actually get rid of them

So I am happy you understand.

In terms of named ranges, anything non-numeric of text characters is considered illegal (i.e spaces, dashes, symbols, apostrophes, quotes, etc).

So we need to create ranges that are free of these chars... and because you need to refer to these ranges indirectly, it is best to name them something that you can build from selections you make in the 3 previous dropdown boxes.

So my formula builds a single string from the combination of selections made in your 3 choices. It then removes all the illegal characters through use of nested Substitute() functions. The resulting string should match a named range exactly and so you can now use INDIRECT to refer to the range and extract the corresponding list for use in your 4th dropdown menu.

You seem to get the just of it. So I hope you can manage. If not, post what you are having trouble with and I will endeavour to help.

Good Luck.

**mtpr220** · 05-17-2010, 04:47 PM

well i took a break for a few days, but im back at it.

I have managed to get it to work by deleting/renaming all of my named ranges.

however, i then tried to implement dynamic ranges for each of the 12 available lists, and i am having difficulty understanding how excel interperets what i am entering.

with a fixed range, you can simply name the appropriate range for each list and then have the 4th drop down cell recognize which list you want to call out by selecting a combination from the first 3 drop downs.

however, with the dynamic range i tried implementing the offset function to specify that i want any additional data in the appropriate column to be added to that columns list. if i am thinking correctly the problem with this is that the "fixed" named ranges i defined before are now obsolete because i need them to be dynamic, and instead i need to find a way to offset from a specified cell depending on what combination is chosen from the first 3 drop downs.

i tried limiting all of my named ranges to just the single top cell for each list and then having the offset function work off of that cell, but so far i am coming up with nothing. i am not sure if i have errors in my syntax or if my method is completely wrong.

make sense? let me know what you think

this is what i ended up with for my 4th drop down source, but it obviously doesnt work:

=offset(INDIRECT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$27&$B$27&$C$27,"(",""),")",""),CHAR(176),"")," ",""))),0,0,counta(INDIRECT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$27&$B$27&$C$27,"(",""),")",""),CHAR(176),"")," ","")))),1)

**NBVC** · 05-17-2010, 04:54 PM

The OFFSET function in the Named Range isn't like that...

It should be a basic formula like:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

so here you would create a named range (which is the combination of the 3 previous possible selections).

e.g. Selection1Selection2Selection3

Say the range you want that to point to dynamically starts in Sheet1, A1,

then use the above formula in the Refers to box.

this will offset from A1 a total number of rows equal to the count of entries made in column A. .... that should be it.

You would repeat referring to a different column for the next set of selections.

The whole Indirect(substitute())) thing is for the data validation List formula to create the name of the range it should look up.

**mtpr220** · 05-21-2010, 05:04 PM

ohhh ok so in trying the method you just described i thought i was on the right track, but i still seem to be stuck at the 4th drop down again. the first 3 drop downs work perfectly and the code to concatenate these 3 categories works great as well. As you suggested, I have set the 4th data validation source to:

=INDIRECT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$4&$A$7&$A$10,"(",""),")",""),CHAR(176),"")," ","")))

where A4, A7, and A10 are the first 3 categories. Next i tested this by naming two distinct ranges using combinations of the 3 categories. The 2 named ranges i tested are as follows

Name:
CONDUITCOPPER60C140F

Value:
{...}

Referrs to:
=OFFSET(LISTS!$D$2,0,0,COUNTA(LISTS!$D:$D)-1,1)

and

Name:
CONDUITCOPPER70C167F

Value:
{...}

Referrs to:
=OFFSET(LISTS!$E$2,0,0,COUNTA(LISTS!$E:$E)-1,1)

where the D and E column of sheet "LISTS" are the respective lists for the distinct ccombinations CONDUITCOPPER60C140F and CONDUITCOPPER70C167F. Im not sure why, but the 4th data validation does not seem to recognize these ranges when ther respective combinations are chosen in the first 3 categories. Assuming i interpreted your instructions correctly, this should work shouldnt it?