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

[Guest]

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.

 

[Guest]

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

 

[Ildhund]

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(D250,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.

 

[Guest]

Thanks a lot!

 

[Don Guillett]

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