Excel 2007 Nested Rows Hi I would like to build a spread sheet to record and man

Question

Excel 2007 Nested Rows

Hi

I would like to build a spread sheet to record and manipulate costs for the products we manufacture. My stumbling block is how to 'nest' my data. Probably not the right terminology - forgive me. So here is what I am trying to achieve.

Our products, widgets, are made up of several key Parts. These Parts are made up of many Components that we buy in.

We sell both complete Widgets and individual Parts. We don't sell Components

I would like a 'table' that lists the Parts and their prices, but with a drop down for each Part which when clicked shows the constituent Components with prices that can be changed. When changed the Parts prices also change. The Parts prices can also have fomulas to calculate totals and sell prices.

Sorry if not clear...

Thanks for help !!

Explanation / Answer

Hi..

I have assumed that some components are shared between parts, and some parts are shared between widgets.

The best approach is to make up five tables:

The first table should list all the components, with entered prices.

Comp1 4

Comp2 6

Comp3 1

Comp4 .55

The second table should list all the parts with all the components that make up the part, like

Part1 Comp1

Part1 Comp2

Part1 Comp3

Part2 Comp2

Part2 Comp4

and the third column of the second should be a VLOOKUP that pulls the price for the components from the first list, so the second table ends up like

Part1 Comp1 4

Part1 Comp2 6

Part1 Comp3 1

Part2 Comp2 6

Part2 Comp4 .55

Similarly, the third table should just list the Parts

Part1

Part2

and the second column should have SUMIF funcctions to sum the prices from the second table, to calculate the price of the part based on components used in the part.

The Fourth table should have the list of Parts in each Widget, with a price column using VLOOKUPs based on the third table to pull the part prices into the table.

The fifth and final table should have the Widget list, and a SUMIF based on the fourth table, to sum the prices of all the parts used in each Widget, to get a widget priice.

I have posted an example workbook here:   http://1drv.ms/1ulBGVT

When the prices for components are updated,, the changes will propogate. You can also add multipliers to account for labor, etc, by changing your SUMIF functions to =SUMIF(.....)*Multiplier, so that a part will cost more than just the total of its components....

Component Comp Price Part Comps Used Comp Price Part Part Price Widget Parts Used Part Price Widget Widget Price

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.