Find matching code & Find first negative value & Define date of the negation & Write it down

mtjanousek

New Member
Joined
Jul 25, 2018
Messages
17
Hello all,
I ran into a problem with my excel file.

I am trying to calculate when I will need to add some additional quantity of material, but the table I am taking data from is not exactly helping.

Fig. 1 shows how the data are approximately displayed.
You can see production for each weak and year and also stock which is slowly going down.

nEuQykwCCW.PNG


The second table (Fig. 2) is trying to use the data from Fig. 1 and find out, based on prescribed coverage of weeks, how much quantity and starting when has to be produced.

gXe6heI5Ta.PNG


I tried to use VLOOKUP but that does not seem feasible at all. Also MATCH and OFFSET. I need to get an equation which is able to:

  1. Find matching material number
  2. Take a look when the stock gets negative
  3. Associate with that information number of week (a number of week before it gets negative) and a year
  4. Calculate how much quantity is needed to cover number of weeks given in the "Coverage"
  5. And write that all down (the yellow cells)

Do you have an idea in what way it would be possible to go? Or do I need to transform the data into some different format using an additional excel sheet?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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
Back
Top