bill of material, assemblies

**jamesw** · 10-18-2011, 01:48 PM

I have a worksheet (Excel 2010) whereby I ask for a quantity of a certain type installation. The answer given by the user then uses that as multiplier of a specific associated assembly.
The assembly then produces bill of material associated with that task and the associated quantities etc.
There are several of these questions that a user will answer and the various assemblies use some of the same parts.
I need to produce a composite bill of material for these materials and there associated total quantities needed.
Vlookup; sum product? I am more of an intermediate excel user so I need help bad...
If it would help I can submit the results of the answers that need to be consolidated...

**shg** · 10-18-2011, 01:53 PM

Welcome to the forum.

Something like this?

       --B-- ----C----- -D- ---E--- ------------------------F------------------------
   2   Level    Desc    Qty Ext Qty                                                  
   3       0 Top Assy    1       1  E3: =D3                                          
   4       1 Sub Assy 1  2       2  E4: =D4* LOOKUP(9E+307, E$2:E3 / (B$2:B3 = B4-1))
   5       2 Bolt1       3       6                                                   
   6       2 Nut1        3       6                                                   
   7       2 Washer1     3       6                                                   
   8       1 Sub Assy 2  2       2                                                   
   9       2 Bolt2       1       2                                                   
  10       2 Sub Assy 3  4       8                                                   
  11       3 Bolt1       4      32                                                   
  12       3 Bolt2       4      32                                                   
  13       1 Bolt1       1       1                                                   
  14       1 Nut1        1       1