Some idea exchange about a proper BOM workbook

[Legacy 143009]

Hi,

I am preparing a BOM project.

Sheet5: Raw Materials
Column A: Raw Material Code
Column B: Raw Material Name
Column C: Raw Material Unit (meter, kg, etc...)
Column D: Raw Material Cost

Sheet4: Semi-Products
Column A: Semi-Product Code
Column B: Semi-Product Name
Column C: Semi-Product Unit
Column D: Semi-Product Cost (Sheet3 is a support table where I relate Semi-Products with Raw Materials and calculate the cost. For example =SUMIF(Sheet3!A:A;Sheet4!B'value';Sheet3!D:D))

Sheet3: Semi-Product support table
Column A: Semi-Product Name (A validation list from Sheet4 Column B)
Column B: Raw Material Name (A validation list from Sheet5 Column B)
Column C: Raw Material Count (How much that raw material is needed.)
Column D: Raw Material Cost (A sub-total for that record row. For example =VLOOKUP(Sheet3!B'value';Sheet5!$B:$D;3;0)*Sheet3!C'value'))
*The formula in Sheet3 Column D now makes more sense.
*So, at this point you can connect more than one raw material to the same semi-product in different rows.

Sheet2: Products List
Column A: Product Code
Column B: Product Name
Column C: Product Unit
Column D: Product Cost (Same thing what we did in Sheet4. Sheet1 is a support sheet where I relate Products wşth sub-parts such as semi-produts or raw materials.)

Sheet1: Products support table
Column A: Product Name (A validation list from Sheet2 Column B)
Column B: Sub-Material Name (A validation list??????)
Column C: Sub-Material Count
Column D: Sub-Material Cost (As the same in Sheet3)

Well, things are starting to get complicated for me at this point... Since I can use both a semi-product or raw material in the same product, I need a validation list which can list both Sheet5 Column B and Sheet4 Column B continuously... But how Sheet1 Column D will know which sheet to lookup to calculate the cost.

Hmm........

I think I found my own answer at this point..... So maybe I can add one more column next to Sheet1 Column A which allows to select what kind of sub-material I want to add to the product. A semi-product or raw material? Hmm.. So I can write an INDIRECT function to the validation list which enables to shift between source sheets. Also I can try the same trick for Sheet1 Column D VLOOKUP formula.

- OK folks, what do you think? Am I on the right track? Is it an appropriate way to prepare a simple BOM?
- My final aim is to visualize the BOM tree with a pivot table where you can expand sub-materials under products in the same column. Also you can monitor the sub costs and total costs for each product. I am planning to use Sheet1 for this, right? I guess there will be no other column labels in pivot table. What do you think? That can I add more in pivot?

Thanks for everyone!

 

[Legacy 143009]

Then, what if a semi-product includes a semi-product?.. Pff