BOM and Estimate Sheet Help

[SNIWOP]

Im trying to update an estimate spread sheet to give me a Bill Of Materials but I'm having trouble achieving this. I will try to explain my problem in hope that someone can help

Currently I have a tab in a spreadsheet for a build of an item. The build has various sub assemblies for the various permutations. These sub assemblies are part numbers/descriptions and costs that change based on selections and ratings from another spreadsheet.
On a front sheet I have a lost of these sub assemblies and I can put a quantity against each type to give me a total cost for the various sub assemblies required for the project.

This works fine for my pricing but what I want to achieve is a bill of material from the selected sub assemblies and their quantities.

Sub assembly	Part A	Part B	Part C	Part D
1.1	P/N 12abc	P/N 34efg	P/N 56hij	P/N 78klm
	$100	$75	$10	$15
1.2	P/N 12abc	P/N 15cfg	P/N 56hij	P/N 78klm

$100

$55

$10

$15

What I want to end up with is a list of parts from Part A, B, C and D and their quantities
What is the best way to achieve this?
Thanks

 

[Michael M]

insert the dollar values into new columns adjacent to the P/N, then use a VLOOKUP formula on the front sheet to return the dollar value

 

[Fazza]

best to set the data up in well structured tables like a database. google for data normalization. then working with the data is MUCH, MUCH, MUCH simpler

 

[SNIWOP]

Thanks for the replies.

I will look at the format and structure of the data I have.

If I have a list of parts in column A and the quantities for each part in an adjacent column B ranging from 0 upwards, what is the best way to give me a bill of materials? Can this done by a formula?

Thanks again

 

[Michael M]

It might be a better idea to upload your workbook to a hosting site, DropBox for instance, then post the link to that file back here.
Remember we can't see your data OR your sheet layout, so what we think is an appropriate answer, may be way off base !!

 

[SNIWOP]

Below is a link to a spreadsheet Im working on.
Basically I'm trying to put together a BOM from the "Drive" tab based on the quantities selected on the front sheet (D24 to D26)
Im hoping to do this with formula's. Code and data base is too hard for me

BOM Sheet

 

[Michael M]

Maybe I need a coffee....but I don't follow what you are trying to achieve !!
I can't seem to relate the data on one sheet to the other !

 

[SNIWOP]

Maybe I need a coffee....but I don't follow what you are trying to achieve !!
I can't seem to relate the data on one sheet to the other !

Sorry, I probably did not explain myself well enough.
On the Drive tab I have various combinations of parts and their prices that I can build up. On the Front sheet tab I can select how many I require of each build I want. I can pull in the pricing and work out my costs but what I am trying to do is create a total bill of materials from each build and times the quantity specified on the front sheet tab. Ultimately give me a complete parts list for the whole project.
On the Drive tab I have multiple columns with parts listed for the various build type. I want to be able to make a list of all of the different parts in these multiple columns and their quantities, if there is a number in the quantity column of the front sheet column D. (D24 to D26)
The spreadsheet is not complete, I have only listed down the first 3 builds on the front sheet (D24 to D26)