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
 

Some videos you may like

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
Joined
Oct 27, 2005
Messages
19,480
Office Version
  1. 2013
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
19,480
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
19,480
Office Version
  1. 2013
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,710
Messages
5,573,743
Members
412,550
Latest member
soking
Top