• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Newbie to Excel, 3 layer drop downs

JustinT

New Member
Hi I have a question regarding 3 deep dependent drop downs.

I have a pile of tools I need to sort of different types some have more or less detail

I have 3 columns, A B C
So Column A is Type, B is Sub Type, C is Material.
A-is a list of tools working fine, brings up options in column B
B-is a dependent list on A that specifies the sup type. Drill, Std, Metric, Letter, Number, etc
C-is for material its made out of.

No matter what I pick for B, the materials are all the same, but working with dependent drop downs seems to me I'd have to make a list for each option in B. (I.E. I have 4 options for col B, then 4 more 'tables' for C with the same options listed. This would be a lot of extra drop downs all with the same information, and since I have over 40 options for col A I could see this getting really messy really fast. Also C has to change because for some tooling I have completely different material options. So any better way to do this?

Thank you
 

JustinT

New Member
I'll have to make a similar file, let me do that and I'll post it to this thread. Est 9am CST
Here is the file sorry for the delay (work got busy), Its pretty simple, But the issue is in Col C. I need to select the option for C based on what is in A. But not B, because each option would result in having to make a list for each answer as I understand it. Whats the best way to go about this. But B is also dependent on A.

Thank you in advance for your help.
 

Attachments

JustinT

New Member
Try............

Select C2 >> Data Validation >>
>> Allow : List
>> Source : =OFFSET('Drop Down Fields'!$A$8,0,MATCH($A2,'Drop Down Fields'!$B$2:$E$2,0),3)
>> OK

Then, copied down

Regards
Bosco
That works! Thank you. Now to play with it.
 

JustinT

New Member
Try............

Select C2 >> Data Validation >>
>> Allow : List
>> Source : =OFFSET('Drop Down Fields'!$A$8,0,MATCH($A2,'Drop Down Fields'!$B$2:$E$2,0),3)
>> OK

Then, copied down

Regards
Bosco
Hi Bosco Yip. I have almost the same data in my real sheet and the issue I'm having is I don't have the extra header row or columns. How do I get it to work? I thought I was good at excel until I saw your code, I had to lookup most of it to see what the heck it does.
 

bosco_yip

Excel Ninja
Without a sample file it is difficult to know your actual situation, so upload to us a sample file with sanitize data.

Regards
Bosco
 

JustinT

New Member
Hope this thread isn't to dead. Please see my data attached, the Nomenclature List is the source. and cols B, C, D are the drop down sources list 1 for A 2-12 for b and 15-20 for c.

Thank you
 

Attachments

Top