assembly, parts, stock - (BOM / inventory/mrp) help

Hi All,

I must say excelforum is a great source for excel advice.

I have a scan through forums with my needs for this task, based on so called BOM, MRP, inventory etc,
though still struggling in formulating what I want.

I know what I want it to do, but getting it to work is another story.

I am new to excel, terms of extensive formulating, only ever used for basic money managing and listing.

On this occasion its a lot more than that. In its Simplest form it is BOM / Inventory list with so many parts creating an assembly
and from this, you order so many assembly's.

I have attached my progress on this using the vlookup functions and data validation but I am now stuck/struggling in formulating,
the ordered number of assembly's and relating that back to their remaining stock at part level.

Typical example:
part01 = 100 stock
part02 = 100 stock
You order 10 asmbly01 which consists of;
part01 x 1
part02 x 2

= 10 - 1 x part01, 10 - 2 xpart02 = current stock of parts

The ordered quantity is required to be driven by validation list. (see attachment)

Hope I have made sense?

All questions welcome and look forward to your responses.

Cheers Sanj

I'm confused on what you need. Do you need the part quantities to show updated values based on assemblies? Maybe this will help:

Try sumproduct(). It's great for finding values based on criteria and multiplying it from there. Here's a quick example

Please Login or Register  to view this content.

Here, It searches through B:B for the value in A2. For each place in b1:b10 where that's true, it multiplies the corresponding values in C1:C:10 and D1:D10.

Sumproduct() is used a lot for this purpose using the "--()" as a criteria check. If you don't quite understand, do a search for sumproduct and you should see examples of this.

Hi there,

thanks for your response.
Yes I require part quantities based on assembly value / allocation. So many assemblies = so many parts showing the remaining stock at part level.
Have had a look as sumproduct and another go at it today and bit of tinkering & I think it may work.

see attached excel doc, values in RED are result formulas.
Currently I have 3 worksheets broken down;
worksheet1 - a parts list (contains details on stock qty & qty required per assembly).
From there another worksheet,
worksheet2 - where I build an assembly from the parts list from worksheet1, using data validation & table management (drop down menu).
Then on the last worksheet,
worksheet3 - is where the orders are put for the assembly, using data validation you select the assembly(as per worksheet2) and enter the qty you require.

Sanj

How can I manage my assembly of a bicycle in excel ?
I mean can I purchase say 100 different parts & build a product which is a bicycle out of it ?