Excel 2007 : Dependent Dropdown (Data Validation)

Hey everyone, I have a somewhat different question about dependant drop down lists in excel 2007.

i have been actively searching dependant drop-down menus for excel for 2 days now, and i have not found an example that explains quite what im looking for.

the problem is that many of these tutorials explain Cascading dependant drop down menus, i.e. 3rd menu dependant on 2nd, 2nd dependant on 1st, almost as if they were in series.

I need a way to develop a dependant drop down menu that works in parallel, for example, I want to generate a drop down list that is dependant on 3 separate categories simultaneously. each of the 3 categories would be independant of one another and have their own drop down selection. The 4th drop down menu would be specific to each possible combination of the 3 independant categories.

In case that explination was too general, ill be more specific. I want to generate a list of available wire types based on 3 categories each having their own drop down menu. this is as far as i have gotten:

1.Run Type (Drop Down):
----->1. In Conduit
----->2. In Cable Tray

2.Wire Material (Drop Down):
----->1. Copper
----->2. Aluminum

3.Temperature rating (Drop Down):
----->1. 60°C (140°F)
----->2. 70°C (167°F)
----->3. 90°C (194°F)

The user will select from each of these 3 drop down menus, and will then be able to select from a fourth drop down menu displaying a list of the available wire types according to the specific combination of the 3 categories available. In total, there are 12 specific lists of available wire types based on every possible combination of category 1,2, and 3.

sorry if this explination is redundant but i wanted to be as specific as possible. I would attatch my file, but it is quite incomplete, and very messy. all i have is the first 3 drop downs, which were relatively easy to learn how to make. thanks in advance for any help.
much appreciated
matt

I think you would need to name the tables names that are a concatenation of each possible combination.. then your list formula would be a concatenation.

So you would have a list named for example:

InConduitCopper60C140F and similar for other lists.

Then your 4th validation would be something like:

Please Login or Register  to view this content.

which replaces the spaces and special characters that are illegal.

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:

Please Login or Register  to view this content.

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*

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.

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.

I'd have to see what you got so far... can you post a sample workbook?

First, you were trying to use dynamic name ranges on your Lists sheet, and those don't work with INDIRECT() Data Validation formulas. I have tried and tried in the past to no avail.

So, the name ranges on the Lists page are now set ranges. That can't be helped, if you need to expand the choices, you will have to adjust the name ranges as well.

Next, I added a new section to the Lists that will allow you to simply lookup the 3-cell concatenation in one column, and get the name that goes with that list in the second column. That makes the INDIRECT() D.V. formula bit shorter.

===========
I added a new feature as well. If you make a choice in column D of Sheet1, then go back to A:C and make changes in your previous choices, you may make selections that invalidate the answer you already have in column D. If that happens, column D value will light up in red to indicate it needs to be reselected. That's done with Conditional Formatting.

mtpr220,

I do apologize. JB is correct in that you cannot use dynamic ranges as an indirect refernce in the data validation Lists... I overlooked that rule.

However, there is a way around it that does allow you to use "dynamic" ranges.

See attached. I only worked on the CONDUITCOPPER60C140F option....

The solution is based on the section in this link under title: Using Dynamic Lists

http://www.contextures.com/xldataval02.html

So, in your Lists Sheet, go back to redefining the name CONDUITCOPPER60C140F as simply the first cell in the column, ie. Lists!$D$1

Then add a new name same as above with suffix, Col. I.e. CONDUITCOPPER60C140FCol and define it as Lists!$D$D (ie. the whole column)

Now your Data Validation list formula is:

Please Login or Register  to view this content.

Notice the reference part of the formula is the same as what I had given you before, and it is again used within the COUNTA() part of the formula, with the addition of the concatenation of the suffix "Col".

Copy the conditional formatting formula out and put it into an empty cell on the same row. CF formulas are by nature TRUE/FALSE tests. If the test resolves to TRUE, the CF is applied.

While the formula is in a cell, you can use the Formula Auditing toolbar to step through that formula one calc at a time and watch it unfold using the Evaluate Formula icon. Very useful tool.

It's Much easier to make a matrix off to the side to refer to the results of the drop down lists name combinations, and the result to return in a column next to the matrix, then the validation will just refer INDIRECT(CE4) or what ever. The key in the matrix is to INDEX(top left to bottom right), Match(""&B1&""&C1&""&D1"",$CB$2:$CB$14),MATCH(top row result column name"Result" top row of matrix $CB$2:$CC$2) and the return value for one of the 12 cominations listed in the column under Column "CB" in Column "CE") This formula goes in Column "CE" and looks up an exact match to serve up to your data validation INDIRECT drop down. By breaking the problem down you are in much better shape for the next issue or change in the demands of your spreadsheet or workbook IMHO.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.