Multiple Drop Down Menus Dependent on First Drop down

[Hoss991476]

I'm Working on a way to quote out jobs easier, and ran in to something that no one has the answer too.

I'm trying to make multiple drop down menus dependent of one drop down

This is the example of what i'm trying to do. column b is the drop down. rows 3,4,5 i'm trying to make dependent of b1 but show only the products related to that row. i have the rest of the excel sheet in name ranges, and prefer if i can do this using name ranges and data validations for simplicity sake

https://u32960789.dl.dropboxusercontent.com/u/32960789/excel/test.xlsx

 

[mart37]

do you mean for B3: =INDIRECT(B1&"_COM")

 

[FDibbins]

Cannot access your file, but see if this will help?

for the DD's
[Table="width:, class:grid"][tr][td] [/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

3​

[/td][td]

Type​

[/td][td]

Category​

[/td][td]

Breed​

[/td][/tr]

[tr][td]

4​

[/td][td]Fish[/td][td]Marine[/td][td]Shark[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]1. create a range name for the main category[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]2. create a range name for each sub-category, based on what it is[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]for mine, the main category is called Type (A2)[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]for the sub categories I give them the same name as in Type (B2:E2)[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td](if you have another level, you would repeat this for eacg subcategory, to get sub-sub-categries)[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]To get the DD's to work...[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]DD for B4 =TYPE[/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]DD for C4 =INDIRECT(SUBSTITUTE(B4," ","_"))[/td][td][/td][td][/td][/tr]
[/table]

for your data...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

1​

[/td][td]Level 1[/td][td][/td][td]Level 2[/td][td][/td][td][/td][td][/td][td][/td][td]Level 3[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Type[/td][td][/td][td]Mammal[/td][td]Bird[/td][td]Fish[/td][td]Bug[/td][td][/td][td]Dog[/td][td]Cat[/td][td]Fresh Water[/td][td]Marine[/td][/tr]

[tr][td]

3​

[/td][td]Mammal[/td][td][/td][td]Dog[/td][td]Domestic[/td][td]Fresh Water[/td][td]Insect[/td][td][/td][td]Doberman[/td][td]Lion[/td][td]Trout[/td][td]Shark[/td][/tr]

[tr][td]

4​

[/td][td]Bird[/td][td][/td][td]Cat[/td][td]Wild[/td][td]Marine[/td][td]Arachnid[/td][td][/td][td]Poodle[/td][td]Tiger[/td][td]Minow[/td][td]Barracuda[/td][/tr]

[tr][td]

5​

[/td][td]Fish[/td][td][/td][td]Cow[/td][td][/td][td][/td][td][/td][td][/td][td]bulldog[/td][td]House[/td][td]Pike[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Bugs[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Hoss991476]

do you mean for B3: =INDIRECT(B1&"_COM")

That would work.... didn't think of that.... Just have to keep in mind of if i changed the name of the name range then i would have to change the formulas as well

Thank you sooo much!