BOM and Estimate Sheet Help

SNIWOP

New Member
Joined
Jul 23, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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 assemblyPart APart BPart CPart D
1.1P/N 12abcP/N 34efgP/N 56hijP/N 78klm
$100$75$10$15
1.2P/N 12abcP/N 15cfgP/N 56hijP/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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,346
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
Joined
May 17, 2006
Messages
9,368
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
Joined
Jul 23, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 27, 2005
Messages
21,346
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
Joined
Jul 23, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
21,346
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
Joined
Jul 23, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,171,650
Messages
5,876,664
Members
433,205
Latest member
jabin1991

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top