Dependent Drop Down Lists

[dham63]

Hi, I am using Office 2003. I have had previous success using dependent drop down lists with nested ifs in a validation statement. Now however I am faced with 19 options which have subcatagory lists, and I am not sure how to get around the limit of 7 nested ifs. I suspect that VBA might be the way to do it but I am a little flumoxed as to where to get started. Do I use the Combo Box from the form toolbar? Any guidance would be appreciated, I know who to create arrays and use else if statements in VBA, its the getting started part here that I am having problems with. Thank you in advance for any assistance

 

[SkipVought]

Hi,

I'm working on an application using this technique, even as we "speak."

I use MS Query to query a sheet with a criteria based on a previous selection. faq68-5829

I'm drilling down 3 levels from the top using this method.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dham63]

Hi Skip, thank you for your reply but I don't think this gives me what I am looking for. I have a drop down list that contains 19 options. I want the next cell to only show the list (I have 19 named ranges on another worksheet) pertaining to the selection they have chosen. Previously I have done this using nested if statements in the source line of the validation box. This time however I have more that 7 entries which makes using nested ifs a non viable option. Am trying to see how I can use vba to create the drop down list with the 19 lists each only showing up when their entry is picked in the previous cell.

Thanks in advance

 

[Gavona]

If your first selection is "Ford", in cell A3, and you have a named range on another sheet called "Ford" then your second validation List would have a source =indirect(A3)

Gavin

 

[SkipVought]

That's EXACTLY what this method does. The selection in one combobox results in a unique subset in another combobox, the selection of which resutls in another unique subset in another combobox etc...

If your selections are named ranges, then that range can be used.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dham63]

Govana, this worked perfectly and is actually much easier than my nested ifs, even for less than 7. Thanks also to you Skip for your answer.