# Is this possible? Help please

#### steve17

##### New Member
Hi

Not sure if this is possible or not.
I have a tab with data called 'WOR' and a second tab called 'SHORTAGE' as shown below.
What I need is a formula that gives the output shown in green.

What it needs to do is look at the part number called up in column B (note there may be many different ones but I've shown just one for simplicity). Find how many are needed and when on the 'SHORTAGE' tab (in this case it's 100off, 500off and 600off for the 23/08, 28/08 and 17/09 respectively). On the 'WOR' tab the first available work order is 881234 (based on its due date) for 150off. Because I need 1200 in total I will need all of the 150off with a date of 23/08/14 to ensure I fulfil the 100off needed for then. This needs to be repeated until the 1200 have been fulfilled. In this instance the last work order (884567) is not needed so the quantity required is zero and if zero is shown the date column should just read 'stock'.

Hope that makes sense,
Steve

 ON A TAB CALLED 'WOR' WORK ORDER # PART # WORK ORDER QTY WORK ORDER DUE DATE QTY REQD DATE REQD 881234 B12345 150 25/08/14 150 23/08/14 882345 B12345 500 28/08/14 500 28/08/14 883456 B12345 800 12/09/14 550 17/09/14 884567 B12345 800 17/09/14 0 STOCK ON A TAB CALLED 'SHORTAGE' PART # QTY REQD DATE REQD B12345 100 23/08/14 B23456 200 25/08/14 B34567 200 26/08/14 B12345 500 28/08/14 B23456 200 29/08/14 B12345 600 17/09/14

<tbody>
</tbody>

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### oldbrewer

##### Well-known Member
why 100 needed by 23/8/14 because you REQUIRE 150 by 25/8/14

#### steve17

##### New Member
why 100 needed by 23/8/14 because you REQUIRE 150 by 25/8/14

Sorry the 'shortage' tab could really be called 'requirements'.

I needed 100 by the 23/08, another 500 by the 28/08 and another 600 by the 17/09.

As each work order completes quite quickly I'm only interested in the earliest date I need that work order to complete, and then an indication of whether I need the full quantity.

Not sure if that makes sense..

#### oldbrewer

##### Well-known Member
do u have 50 in stock already then?

#### steve17

##### New Member
do u have 50 in stock already then?

No, none in stock.
The first w/o (881234) for 150off will supply 100off needed on the 23/08 and 50off towards the 500 needed on the 28/08. It's the earliest date required (23/08) that I then want to show. Full quantity (150off) is needed though, just in two different time frames...

Replies
4
Views
390
Replies
6
Views
270
Replies
0
Views
189
Replies
1
Views
1K
Replies
6
Views
367

1,190,945
Messages
5,983,797
Members
439,859
Latest member
AlunM

### 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.

### Which adblocker are you using?

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

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