BOM and Estimate Sheet Help

SNIWOP

New Member
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

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Michael M

Well-known Member
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

MrExcel MVP
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

New Member
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

Well-known Member

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

New Member
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

Well-known Member
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

New Member
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)

Replies
12
Views
297
Replies
7
Views
85
Replies
1
Views
202
Replies
1
Views
262
Replies
3
Views
67