Really Need Help 3.

[Sara]

Hi, Now I have already made a complete list of data with no blank cell. I
would like to know whether it is possible 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 WITHOUT PERFORMING SORTING? 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 12-Jan-07, stock is not enough for
13-Jan-07 delivery . Is there any lookup or other formula that can tell me
the date without performing sorting? I have to input data everyday upon
received PO from customer. Now my method of doing is to perform sort every
time after input new information, then in columm E, I add up the qty in PO
QTY, then using formula to find the date.
=INDEX(C2:C4500,MATCH(0,E2:E4500,-1)+0)-0

But the problem is I have to perform sort every time after input new data
and to rearrange the formula by (drag to cover all new items). Is there any
formula that can return the result of the date 'out of stock' and yet data
sorting is not required?

Thank in advance.


A B C D
1 Part name stock Delivery date PO QTY
2 Screw AB 55,000 1-Jan-07 10,000
3 Screw AB 2-Jan-07 10,000
4 Screw AB 3-Jan-07 10,000
5 Screw AB 4-Feb-07 10,000
6 Screw MN 45,000 5-Feb-07 10,000
7 Screw MN 6-Feb-07 10,000
8 Screw MN 11-Jan-07 20,000
9 Screw AB 12-Jan-07 10,000
10 Screw AB 13-Jan-07 10,000
11 Screw AB 4-Feb-07 10,000
12 Screw MN 15-Feb-07 10,000
13 Screw MN 16-Feb-07 10,000

 

[driller]

maybe visual highlight of the row in red can alarm you for the supposed date
of "out of stock"
select the whole row2
conditional format
formula is: =SUM($C$2:$C2)<SUM($E$2:$E3)
choose red color..

copy Row(2) and paste special format to the next rows..

observe the PO qty. that is not availed with stock...must be colored as red

 

[pinmaster]

Hi,

Not sure this will help but maybe something like this:

=MIN(IF((A3:A14="screw ab")*(E3:E14<=0),C3:C14))

enter with CTRL+SHIFT+ENTER

HTH
Jean-Guy

 

[KC Rippstein hotmail com>]

It would definitely help if you could put your inventory tracker on one sheet
and purchase order transactions on another. I just did a sample file for
someone else on here the other day and you may still be able to see it at
http://rippstein.groupfiles.com. I designed it so the inventory is updated
as soon as the PO is entered, and when you try to enter a PO for more product
than you have on hand, it tells you that is an invalid sale. I basically
used sumif for the inventory tracking and vlookup with conditional formatting
on the transactions page. I'd be happy to email it to you or just explain it
here tomorrow (Thursday).

- KC Rippstein

 

[Sara]

Hi, Is it possible to return same value (date 'out of stock') without create
columm E (adding up total quantity required)?

Thanks
Sara