Stock control formulas, counting and reporting remaining stock

[santaviga]

Hi, I am looking for various formulas for a stock control spredsheet.


a1 a2 a3
a4 ref cell
Date Stock In/Out Balance 100(start stock)
1/1/06 -20 need this cell to relate to 80
2/1/6 +20 need this cell to relate to 100 etc
no entry to appear until figure
is input into column a2


In cell A6 I need a formula to report last entry if stock is less than 50 to
say Order Stock and in RED if stock is greater than 50 say Stock OK and in
Black.

Help on this would be very much appreciated.

Regards,


Santaviga

 

[JLatham]

I think you meant:
A B C D
1 Date In/Out Balance 100 (start stock)
2 1/6 -20 80 OK
3 1/6 +20 100 OK
4 1/7 -55 45 ORDER (in red)
5

IN C2, enter this formula
=IF(B2<>0,B2+D1,"")
In D2, enter this formula
=IF(C2<>"",IF(C2>50,"OK","ORDER"),"")

In C3, enter this formula
=IF(B3<>0,B3+C2,"")
and in D3, enter this formula
=IF(C3<>"",IF(C3>50,"OK","ORDER"),"")

You may now fill the formulas down the sheet as far as needed. To get the
black "OK" and red "ORDER" entries, use Format | Conditional Format on the
cells in column D. Choose Value is Equal To and type in ORDER and choose the
format for the font then.

 

[santaviga]

Thats absolutely great. thanks a lot.

Santaviga

I think you meant:
A B C D
1 Date In/Out Balance 100 (start stock)
2 1/6 -20 80 OK
3 1/6 +20 100 OK
4 1/7 -55 45 ORDER (in red)
5

IN C2, enter this formula
=IF(B2<>0,B2+D1,"")
In D2, enter this formula
=IF(C2<>"",IF(C2>50,"OK","ORDER"),"")

In C3, enter this formula
=IF(B3<>0,B3+C2,"")
and in D3, enter this formula
=IF(C3<>"",IF(C3>50,"OK","ORDER"),"")

You may now fill the formulas down the sheet as far as needed. To get the
black "OK" and red "ORDER" entries, use Format | Conditional Format on the
cells in column D. Choose Value is Equal To and type in ORDER and choose the
format for the font then.