Hi Sara

I feel that we only have part of the picture here, as I don't see how
stocks ever get increased to meet orders, unless you enter deliveries as
negative purchase orders.
However, on the basis of the information provided. do the following.
Insert a new row 1, pushing your headers to row 2
In all cases, adjust the total length of the range to suit your data

In B1
=VLOOKUP(A1,$A$3:$B$100,2,0)
in E1
=INDEX(E3:E100,MATCH(F1,F3:F100,0))
in F1 enter the array formula with CSE
{=MIN(IF(F3:F100TODAY(),F3:F100,""))}

In E3
=$B$1-SUMIF($A$3:$A3,A$1,$D$3:$D3)
in F3
=IF(E3<0,C3,"")
copy E3:F3 down as far as required, having adjusted the range

Now, if you enter ScrewAA in cell 1, it will show the relevant date in
F1 and the negative quantity that would be created by that PO in E1.
Change values in A1 to bring up data for other products.

--
Regards

Roger Govier


"Sara" wrote in message
...
Question 1. I need a formula to return the furthest delivery date per
a
particular part. Eg. I want the aerliest delivery date for Screw AA,
the
answer is 1-Jan-07.

Quastion 2 : This is the one that makes me crazy thinking better ways
to
solve. I want to have a formula that can automatically return the date
of a
particular product will out of stock based on customer's order. Please
refer
to table below. I have 35,000 Screw AA left in finish good store.
Based on
actual customer's PO, I can support customer's demand until 4-Jan-07,
stock
is not enough for 3-Feb-07 delivery . Is there any lookup or formula
that can tell me the date WITHOUT have to sorting data (in sequence
order)?
My current method of doing : I will sort data by columm A after input
all new
PO information. Then, in columm B, I use Vlookup formula to find data
from
another worksheet. Then, in columm E is Stock-PO Qty, drag from the
top row
to bottom row of each Part Name. Then, create formula in columm F :
(=MIN(IF((A$2:A$10000=A2)*(E$10:E$10000<=0),C$2:C$ 10000))) enter with
CSE.
Then, copy this formula to every top row of each item.

The above mention formula is workable. but it really takes a lot of
time to
accomplish. Input PO data, Sort Data, Create lookup, create Balance
qty
formula, and create formula in columm F and finally drag/copy each
item by
item. IS there any better ways to accomplish this? ESPECIALLY Without
have to
SORT and COPY and PASTE, DRAG manually item by item??? Is there any
formula
to return the date even without columm F?

Columm 'A' contains more than 1,500 different
part name.

A B C D E F
1 Part name stock Delivery date PO QTY Balance Qty Out
Date
2 Screw AA 35,000 1-Jan-07 22,000
3-Feb-07
3 Screw BB 35,000 2-Jan-07 18,000
2-Feb-07
4 Screw CC 35,000 3-Jan-07 20,000
3-Feb-07
5 Screw AA 4-Jan-07 10,000
6 Screw BB 5-Jan-07 10,000
7 Screw CC 6-Jan-07 10,000
8 Screw AA 3-Feb-07 12,000
9 Screw BB 2-Feb-07 10,000
10 Screw CC 3-Feb-07 10,000

Desperately need help.

Thanks.
Sara