Need Help. Lookup or sum technic that returns a date.
 

Hi, Anyone can help to solve my problem please?

I want to create a lookup formula that can return the result of date which a
particular product will out of stock based on customers order. Please refer
to table below. I have 55,000 Screw AB left in finish good store. Based on
actual customers PO, I can support customers demand until 5-Jan-07, stock
is not enough for 6-Jan-07 delivery . Is there any lookup or other formula
that can tell me the date? I have many items to check weekly, Really
appreciate that someone here can help to ease my problem.

A B D E
1 Part name stock Delivery date PO QTY
2 Screw AB 55,000 1-Jan-07 12,000
3 2-Jan-07 10,000
4 3-Jan-07 10,000
5 4-Jan-07 10,000
6 5-Jan-07 10,000
7 6-Jan-07 10,000

Formula : ?

Thanks in advance.
Good day.

Need Help. Lookup or sum technic that returns a date.
 

One way ..

In F2: =SUM($E$2:E2)
Copy F2 down to F7

Then place in say, G2:
=INDEX($D$2:$D$7,MATCH(C2,$F$2:$F$7))
Format G2 as date. G2 returns the required date
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sara" wrote:
Hi, Anyone can help to solve my problem please?

I want to create a lookup formula that can return the result of date which a
particular product will out of stock based on customers order. Please refer
to table below. I have 55,000 Screw AB left in finish good store. Based on
actual customers PO, I can support customers demand until 5-Jan-07, stock
is not enough for 6-Jan-07 delivery . Is there any lookup or other formula
that can tell me the date? I have many items to check weekly, Really
appreciate that someone here can help to ease my problem.

A B D E
1 Part name stock Delivery date PO QTY
2 Screw AB 55,000 1-Jan-07 12,000
3 2-Jan-07 10,000
4 3-Jan-07 10,000
5 4-Jan-07 10,000
6 5-Jan-07 10,000
7 6-Jan-07 10,000

Formula : ?

Thanks in advance.
Good day.

Need Help. Lookup or sum technic that returns a date.
 

If your column headings are in row 1, delivery dates in column D and order
quantities in column E, you could
(a) in F1 put =B2 (your current stock)
(b) in F2 put =F1-E2 and copy down
(c) in G1 put Reorder date
(d) in G2 put =INDEX(D2:D50,MATCH(0,F2:F50,-1)+1)-1 and format the cell as a
date.

The figures in F give you a running stock total after dispatching that day's
order. The last "1" in the formula gives you the date one day before you
would run dry if you didn't replenish your stocks. If you need more notice,
change this 1 to the number of days' notice you need. The 50 is the maximum
number of possible deliveries you might preprogramme - adjust to suit.

--
Noel

"Sara" wrote in message
...
Hi, Anyone can help to solve my problem please?

I want to create a lookup formula that can return the result of date which
a
particular product will out of stock based on customers order. Please
refer
to table below. I have 55,000 Screw AB left in finish good store. Based on
actual customers PO, I can support customers demand until 5-Jan-07,
stock
is not enough for 6-Jan-07 delivery . Is there any lookup or other formula
that can tell me the date? I have many items to check weekly, Really
appreciate that someone here can help to ease my problem.

A B D E
1 Part name stock Delivery date PO QTY
2 Screw AB 55,000 1-Jan-07 12,000
3 2-Jan-07 10,000
4 3-Jan-07 10,000
5 4-Jan-07 10,000
6 5-Jan-07 10,000
7 6-Jan-07 10,000

Formula : ?

Thanks in advance.
Good day.

Need Help. Lookup or sum technic that returns a date.
 

Thanks a lot!

Need Help. Lookup or sum technic that returns a date.
 

A macro assuming dates in col b and amounts in col c
Sub sumuntil()
available = 55000
For i = 2 To Cells(Rows.Count, "c").End(xlUp).Row
ms = ms + Cells(i, "c")
If ms + Cells(i + 1, "c") = available Then Exit For
Next
MsgBox Cells(i, "b")
End Sub
--
Don Guillett
SalesAid Software

"Sara" wrote in message
...
Hi, Anyone can help to solve my problem please?

I want to create a lookup formula that can return the result of date which
a
particular product will out of stock based on customer's order. Please
refer
to table below. I have 55,000 Screw AB left in finish good store. Based on
actual customer's PO, I can support customer's demand until 5-Jan-07,
stock
is not enough for 6-Jan-07 delivery . Is there any lookup or other formula
that can tell me the date? I have many items to check weekly, Really
appreciate that someone here can help to ease my problem.

A B D E
1 Part name stock Delivery date PO QTY
2 Screw AB 55,000 1-Jan-07 12,000
3 2-Jan-07 10,000
4 3-Jan-07 10,000
5 4-Jan-07 10,000
6 5-Jan-07 10,000
7 6-Jan-07 10,000

Formula : ?

Thanks in advance.
Good day.