Making a cost table with... "drop downs"..?

[Pen.Cap.Chew]

Hi there,

First off i haven't used Excel in a long long time and only have basic knowledge.

I'll do my best to explain what i'm trying to do. I was told Excel was the "man for the job" in doing this task.

I'm trying to make a cost vs gain chart, but its a bit more complex as i need Excel to do a "drop down" i guess is the best word.

Here is an example, a poor one... but you get the idea:

I need to build 2 chairs.

1 Chair requires:

2x4 Lumber: 4 pieces @ $100 each = $400 total
Nails: 30 @ $1 each = $30 total
1 chair = $430
2 chairs = $860

That's easy enough. Here is where i run into my problems.

Now i need to build 1 kitchen set

1 kitchen set requires:

4 chairs
1 table

What i would like Excel to do is when i select my "kitchen set" have the supplies break down as 4 chairs and 1 table (total cost of each and total together) AND have it also list the supples needed to build each piece. (table and chair).

So it would look something like this:

Kitchen set = $2,350

4 chairs

2x4 lumber: 16 pieces @ $100 = $1,600
Nails: 120 pieces @ $1 = $120

Total: $1,720
​

1 Table

2x6 lumber: 3 pieces @ $200 = $600
Screws: 20 pieces @ $1.50 = $30

Total: $630
​

Notice how things drop down to show the costs of the supplies needed to build 1 item in order to build the other item?

This is small scale, in the end i'd like to add things where you would be required to build 2-3 things to build another, that would be added to 5-6 other items all required to build another, bigger item and so on and so on.


Thanks in advance for any help