variable drop-down list for inventory list

Hello everyone. Im trying to organize a tools inventory list. Its supposed to be sort of like an order sheet for each department. But what I want to do is when I select either CARPENTRY, or any of the other trades from a drop-down list, I'll be able to select from another drop-down list items associated with that trade in particular. So eventually I want the master data hidden, preferably in another sheet. I searched all of Google but it seems a bit complicated to get it done. Any help is appreciated. I've attached the sheet so that you can see what I'm talking about. MOVE IN MASTER LIST.xlsx

Wow....you're fast..lol. Very nice. It's exactly what I want. Now let me get this straight: 1. I dont need the tool list in the "DEPT ORDER" sheet? 2. How would I go about expanding any of the tool list? or Do I just expand the cell range in the formula? 3. For internal use we need to know the quantity of each item, can I just add columns next to each Dept. or do I also have to edit the formula as well? I really appreciate all you help with this.

Originally Posted by FDibbins

Hi and welcome to the forum

See if the attached is what you want? I rearranged your data and put it on sheet 2 (now called Data). I then created a named range for each tool type, and used INDIRECT() to pull in teh data for each selected tool type.
To see the names, look under Formula/Defined names/Name manager
To see the Dop-down formula, look under Data/Data Tools/Data Validation

Ok I see. As far as pulling the Qty's, I don't think it'll be necessary because the purpose is that each dept input how much of each item they'll need, and from the data list we can determine whether we need to get more, or if we have enough. Now I seem to have run into a small problem. It seems as if the drop down list only goes for about 30 rows in the "DEPT ORDER" sheet, how can i expand that to at least two pages? Also, the CARPENTER drop down items don't show up when it's selected. I've attached the sheet to see the minor changes that I've done. Thanks beforehand.

Originally Posted by FDibbins

1. You dont need columns A:D (or maybe A:E) on DEPT ORDER SHEET. column F contains the Drop-Downs, so you need that column. If you want to add QTY's then maybe keep E

2. You would need to expand the range of the range name (in Name Manager)

3. If you want to pull in QTY's, you could probably use vlookup().. If you provide a sample of what you want, I can work on it for you