Dependent Drop Down Lists Referencing Multiple Worksheets

[kreiter]

This is my first post so I hope to get some good easy feedback.

I am making a document for quoting materials and the first worksheet is quoting, all others will be Material Data Sheets. I want to make a drop down list on the first worksheet that can choose from any of the available materials (done) then depending on what material is selected, I want it to give choices in the drop down of the available thicknesses of said material. Possible?

For further explanation, if rubber has thicknesses of 1,2, and 3" and cork has thicknesses 4,5, and 6, if rubber is selected I don't want 4,5, or 6 available to choose. Thanks!!!

 

[XLfriend777]

Hi kreiter - Try using Data Validation to generate the Dropdown List and then in the Data Validation range use =INDIRECT(A1). In Cell A1 you can then place a changeable Named Range calling out your various options. As cell A1 changes, so will the options in the Dropdown List. Check out the website reference at: http://www.contextures.com/xlDataVal02.html Hope this helps.

 

[kreiter]

goesr,

I have tried that but I keep getting confused being that each selection in the drop down list has it's own worksheet. so each selection would reference a list in a different worksheet. For example, Worksheet 1 has drop down list of materials A,B,C, and D (All materials have their own worksheets A,B,C, and D) A has options 1 and 2, Material B has 3 and 4, etc. If I select material B I would like the second drop down list to have options 3 and 4. So the problem I'm running into is with multiple worksheets and the indirect function is proving difficult and complex...

 

[XLfriend777]

Hi kreiter - I was able to define a range in another workbook using the format: Range4 =[Book1.xlsx]Sheet1!$A$1:$A$4.

Hope this helps.

 

[kreiter]

And it makes it more difficult that the name of the cells can not have spaces and from what I've seen need to be an exact match to what my first drop down list selections are.