Trying to add drop down list referencing to multiple column Table?

[Herrerag92]

Hello hoping someone can help me on a project im currently working to keep Inventory Stock on sheet Metal at work.

Im trying to add a Drop Down list on a My Sheet called "Material Log" On the First Column named Material i want a drop down list that states the materials that are reference on the table on my second Tab called Material Insert. On the next column under sheet size i want a drop down list that states all the sheet sizes that are available with the material chosen on the the first column.

I hope im clear on what im trying to do any help will be greatly appreciated. Im new to excel trying to learn as i go.

Thank You,

[dflak]

What you are trying to do is called a cascading drop-down list where the selection of one drop-down item determines the selections available for the next drop-down list.

You can look up cascading drop-down lists on the web, but most of those techniques are not very flexible. Your data looks like it is suitable for the non-VBA method shown in the first link below. Let me know if you need help with it. You are only going two levels deep so you won't need the COUNT column. Also included is a link on how to do named dynamic ranges which is also needed for this to work.

http://www.utteraccess.com/wiki/Casc..._%28Non-VBA%29
http://www.utteraccess.com/wiki/Casc...ists_%28VBA%29

http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges

[JBeaucaire]

1. You'll need a secondary list, it's pretty easy to simply do a Filter for unique values on the first column of the table to extract the alphabetized list into a clean column.
2. Then we create a dynamic named range of that secondary list. So you can add or subtract items in that column and the named range MaterialList adjusts itself.
3. We add the Data Validation in column A to use the new named range MaterialList. (working)
4. For the dynamic list of options, another dynamic named range formula can accomplish this. However, these don't work if the source material is an Excel "Table". It must be a normal range. So for now I copied your Table to the right, then made a named range Material2 to cover this range.
5. Now we add a DV formula in column B to present a list of Options based on the column A choice.
6. =OFFSET(Material2,MATCH(A2,OFFSET(Material2,,,,1),0)-1,1,COUNTIF(OFFSET(Material2,,,,1),A2),1)

It's all working in the attached file.


Dynamic Cascading Drop Downs using OFFSET

[Herrerag92]

Thank You very much, Your reply and link to Dynamic Cascading Drop Downs helped very much.