List Question

I'm working on a spreadsheet for tracking certain commodities. These commodities have different styles in which they can be packed, and different sizes that each style can be packed as. Example:

The Widget commodity can be packed in single layer boxes, two layer boxes, or just dumped into one big box. The single layer boxes can hold 10" widgets, 15" widgets, or 20" widgets. The two layer boxes can hold 8" widgets, 12" widgets, 15" widgets, or 25" widgets. The big box can hold any size of widget.

It's important that the spreadsheet is able to track specifically by size, style, and commodity, so that I can take a look at my summary section to see an instant snapshot of, for example, how many 20" widgets were packed into single layer boxes for any given day. The formulas for tracking are all created, everything works right, the only issue is this. The spreadsheet is extremely large and convoluted, because I have to provide space for any commodity, any style, and any size packaging for every customer. A lot of these rows are excess, and unnecessary, but can not be removed, on the off chance that they may be needed in the future.

What I want to do is this:

I want to make, say, three rows for each commodity (sprockets, widgets, thingies, etc). Then, I want to create a drop-down list from which the packing style can be chosen (single layer, two layer, big box, etc). I then want to create a sizing list (8", 10", etc) that will display the correct size for the correct style. So, if I were to go to the Widgets row and choose the Single Layer style, I want the size drop-down list to automatically (based on what is chosen in the style cell) present me with a drop-down of appropriate sizes for that style. That is where I run into a problem, because I have no idea on how to accomplish that. I can create the lists using Data -> Validation -> List for each cell, but the trick of making the size list result dependent on the style list result escapes me.

Thanks!

Originally Posted by protonLeah

Sorry, but this looks suspiciously like a school project that you want someone else to complete. Can you post a file with any work you have done on your own to solve the problem?

I can see how it would look that way, what with the widgets, sprockets, etc. I work for a fruit company in California, and the spreadsheet we've developed is something we'd not like to get out into public hands. I'll work on making an example to put up when I have the time.