# 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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### 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)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,958
Messages
5,856,518
Members
431,819
Latest member
hellosunshine

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back