Simple MRP for excel?

**a94andwi** · 10-30-2009, 12:16 PM

Hello.

I run a production planning departement and I need to do a simple mrp from the current production plan.
The production plan is made in excel:

Day 1 Day 2
Part 1 100pcs 200pcs
Part 2 20 pcs 10pcs

In Part 1 and 2 there are several part numbers that are assembled to it.
I have chart with these part numbers like this:
Part100 Part200 Part300
Part 1 3pcs 2pcs 3pcs
Part 2 23pcs 10pcs 21pcs

I would like to have place in my chart that multiplies the current production plan for all part numbers and creates a total sum of demand for part100, part200 and so on.

I have attached an document with a lot of Index and Match functions but I can't make this work.

Is there som kind person with the knowledge to hack this problem?

/Anders

**JBeaucaire** · 10-30-2009, 01:02 PM

I believe this formula in C26, then copied down and across:

=SUMPRODUCT(($C$3:$F$3=$A26) * ($C$4:$F$8*C$17:C$21))

**a94andwi** · 10-30-2009, 01:14 PM

Easier than I ever could imagined. Thanks alot for the help!!!!!

/Anders

**a94andwi** · 10-31-2009, 04:25 AM

Hello again.

The attached file you were looking at was only a model to make it work. I have now made a new file with 3 sheets containing part structure, production plan and part demand.

Now when I simply change the formula to point to other sheets I get N/A# error.

I can't understand why? Can you see what I've done wrong?

I attach a new fil where the formula is in sheet "Materialbehov" C4.
Materialbehov - > Material demand (MRP)
Körplan ->Production plan
Struktur -> Part structur

Please help!

/Anders

**martindwilson** · 10-31-2009, 05:08 AM

you have put the wrong cell ref in the first part
Struktur!$C$2:$Q$3
should be
=SUMPRODUCT((Struktur!$C$2:$Q$2=Materialbehov!$A4)*(Struktur!$C$4:$Q$103*Körplan!C$4:C$103))
i think

**a94andwi** · 10-31-2009, 05:56 AM

Thanks again.

I knew there must be a simple solution. The first thing that goes blind are the eyes! :-)
/Anders

**JBeaucaire** · 10-31-2009, 05:04 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

**Steve13** · 05-22-2019, 05:07 AM

First post, so be kind.

But the original file is no longer available, could someone resurrect this file or help me with this?

I essential have 3 different groups of components in builds, with 3 duplicates products across the 3 parts.

I need a simple forumla to totalise the totals to be ordered to build the 3 required kits.

**dominicb** · 05-22-2019, 07:10 AM

Good afternoon Steve13

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread