Is this possible? Help please

[steve17]

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,
Thanks in advance
Steve


[TABLE="width: 540"]
<tbody>[TR]
[TD="colspan: 2"]ON A TAB CALLED 'WOR'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WORK ORDER #[/TD]
[TD]PART #[/TD]
[TD]WORK ORDER QTY[/TD]
[TD]WORK ORDER DUE DATE[/TD]
[TD]QTY REQD[/TD]
[TD]DATE REQD[/TD]
[/TR]
[TR]
[TD]881234[/TD]
[TD]B12345[/TD]
[TD]150[/TD]
[TD]25/08/14[/TD]
[TD]150[/TD]
[TD]23/08/14[/TD]
[/TR]
[TR]
[TD]882345[/TD]
[TD]B12345[/TD]
[TD]500[/TD]
[TD]28/08/14[/TD]
[TD]500[/TD]
[TD]28/08/14[/TD]
[/TR]
[TR]
[TD]883456[/TD]
[TD]B12345[/TD]
[TD]800[/TD]
[TD]12/09/14[/TD]
[TD]550[/TD]
[TD]17/09/14[/TD]
[/TR]
[TR]
[TD]884567[/TD]
[TD]B12345[/TD]
[TD]800[/TD]
[TD]17/09/14[/TD]
[TD]0[/TD]
[TD]STOCK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]ON A TAB CALLED 'SHORTAGE'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PART #[/TD]
[TD]QTY REQD[/TD]
[TD]DATE REQD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B12345[/TD]
[TD]100[/TD]
[TD]23/08/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B23456[/TD]
[TD]200[/TD]
[TD]25/08/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B34567[/TD]
[TD]200[/TD]
[TD]26/08/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B12345[/TD]
[TD]500[/TD]
[TD]28/08/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B23456[/TD]
[TD]200[/TD]
[TD]29/08/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B12345[/TD]
[TD]600[/TD]
[TD]17/09/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

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

 

[steve17]

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]

do u have 50 in stock already then?

 

[steve17]

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