Date Stock In/Out Balance Stock Remaining
20 120 Stock OK
20 140 Stock OK
30 170 Stock OK
-50 120 Stock OK
-60 60 Stock OK

I have the above set out in a worksheet, What I need is a formula to put in
a cell for the following: I need a cell e.g A10 to recognise the last entry
in column stock remaining. e.g the cell would return Stock OK or Order stock,
It need to be the last entry in the column and it must update when later
entries are entered into the Stock Remaining.

Can anyone help ????

Regards.


Santaviga

With
your posted data structure beginning in cell A1
Col_A contains Date
Col_B contains Stock In/Out
Col_C contains Balance
Col_D contains Stock Remaining

This returns the last status of Col_D corresponding to the last Stock In/Out
entry:
E1: =INDEX(D:D,MATCH(10^99,B:B))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"santaviga" wrote:

Date Stock In/Out Balance Stock Remaining
20 120 Stock OK
20 140 Stock OK
30 170 Stock OK
-50 120 Stock OK
-60 60 Stock OK

I have the above set out in a worksheet, What I need is a formula to put in
a cell for the following: I need a cell e.g A10 to recognise the last entry
in column stock remaining. e.g the cell would return Stock OK or Order stock,
It need to be the last entry in the column and it must update when later
entries are entered into the Stock Remaining.

Can anyone help ????

Regards.


Santaviga

Hi Ron,

This formula will not enter it is coming up as text, It needs to return the
last data in the column.

Column D has a formula in it to return Data if C falls below 50 it will
report ORDER STOCK, if C is greater than 50 it will return Data Stock OK, I
need to show the last data in D whether Stock OK or ORDER STOCK to show in
one cell at the top of the spreadsheet.

Your help is greatfully appreciated.

Regards,


Mark

"Ron Coderre" wrote:

With
your posted data structure beginning in cell A1
Col_A contains Date
Col_B contains Stock In/Out
Col_C contains Balance
Col_D contains Stock Remaining

This returns the last status of Col_D corresponding to the last Stock In/Out
entry:
E1: =INDEX(D:D,MATCH(10^99,B:B))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"santaviga" wrote:

Date Stock In/Out Balance Stock Remaining
20 120 Stock OK
20 140 Stock OK
30 170 Stock OK
-50 120 Stock OK
-60 60 Stock OK

I have the above set out in a worksheet, What I need is a formula to put in
a cell for the following: I need a cell e.g A10 to recognise the last entry
in column stock remaining. e.g the cell would return Stock OK or Order stock,
It need to be the last entry in the column and it must update when later
entries are entered into the Stock Remaining.

Can anyone help ????

Regards.


Santaviga

Hi Ron,

Sorry this does work was entering wrong formula, Thanx a great deal, much
appreciated.

Regards


Mark

"Ron Coderre" wrote:

With
your posted data structure beginning in cell A1
Col_A contains Date
Col_B contains Stock In/Out
Col_C contains Balance
Col_D contains Stock Remaining

This returns the last status of Col_D corresponding to the last Stock In/Out
entry:
E1: =INDEX(D:D,MATCH(10^99,B:B))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"santaviga" wrote:

Date Stock In/Out Balance Stock Remaining
20 120 Stock OK
20 140 Stock OK
30 170 Stock OK
-50 120 Stock OK
-60 60 Stock OK

I have the above set out in a worksheet, What I need is a formula to put in
a cell for the following: I need a cell e.g A10 to recognise the last entry
in column stock remaining. e.g the cell would return Stock OK or Order stock,
It need to be the last entry in the column and it must update when later
entries are entered into the Stock Remaining.

Can anyone help ????

Regards.


Santaviga

Assume the colume is C. Then the cell monitoring the last cell in colume C
would be

=offset(C1,counta(C:C)-1,0)

Assume you don't have anything down below the data you are showing to us.

"santaviga" wrote:

Date Stock In/Out Balance Stock Remaining
20 120 Stock OK
20 140 Stock OK
30 170 Stock OK
-50 120 Stock OK
-60 60 Stock OK

I have the above set out in a worksheet, What I need is a formula to put in
a cell for the following: I need a cell e.g A10 to recognise the last entry
in column stock remaining. e.g the cell would return Stock OK or Order stock,
It need to be the last entry in the column and it must update when later
entries are entered into the Stock Remaining.

Can anyone help ????

Regards.


Santaviga