Bill of Material Explosion

[bluetaurean]

I have two sheet, the first has the demand of each product

Product Qty

a 5

b 6

The second sheet has the bill of material (what is needed to make the product) for each product

Product Material Qty

a Materia a 1

a Materia c 2

a Materia g 4

a Materia g 7


b Materia a 1

b Materia c 2

b Materia g 4

b Materia g 7


I need to list of consolidated demand for each material. One way is to do a lookup of the demand in the 2nd sheet from the first and then pivot the data. Is there a better or faster way.

 

[NARAYANK991]

Hi ,


In your second sheet , have the following formula :


=C2*SUMPRODUCT((Sheet2!$A$2:$A$3=Sheet1!A2)*(Sheet2!$B$2:$B$3))


where :


C2 contains the Bill of Material quantity of material "a" in Sheet1


A2:A3 contain the products "a" and "b"


B2:B3 contain the product quantities for products "a" and "b"


Narayan

 

[dan_l]

Bill of Material Explosion was the name of my band in high school.

 

[NARAYANK991]

Hi Dan ,


Nice one. I would have thought Piracy of the High Cs would have been better.


Narayan