I want to write a function that displays the last value populated ina range.
To help explain please see below:

column A B C D E F G
H
row
1 67 55 42 66 49 39 72
56
2 56 38 44 77 59
3
4

This is what I'm trying now, doesn't work.
=IF(COUNT(a1:h4),LOOKUP(99^99,a1:h4),"-")

I want to be able to display in this case the value 59.


I need to write a function that evaluates A1:H4 and displays the last value
populated. In this case 59.





--
Sony Luvy

*Maybe* this:

=IF(COUNT(A1:H4),LOOKUP(1E100,INDEX(A1:H4,MAX(INDE X((A1:H4<"")*ROW(A1:H4),,)),)),"-")

It will return the last numeric value from top to bottom, left to right.

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
I want to write a function that displays the last value populated ina
range.
To help explain please see below:

column A B C D E F G
H
row
1 67 55 42 66 49 39 72
56
2 56 38 44 77 59
3
4

This is what I'm trying now, doesn't work.
=IF(COUNT(a1:h4),LOOKUP(99^99,a1:h4),"-")

I want to be able to display in this case the value 59.


I need to write a function that evaluates A1:H4 and displays the last
value
populated. In this case 59.





--
Sony Luvy

Biff, I just integrated the function below and got a #REF! error, and know
why. I didn't present the problem exactly right. The cell range is not
contiguous, so A1:h4 does not accurately reflect the cell range. The actual
cell ranges to "lookup" a

b57:ae57,b64:ae64,b71:ae71

This non-contiguous range must be read to identify the most recent (or last)
value populated.

Can you please modify your function below to accommodate the non-contiguous
range above?

Thanks for your help. - Tom
--
Sony Luvy


"T. Valko" wrote:

*Maybe* this:

=IF(COUNT(A1:H4),LOOKUP(1E100,INDEX(A1:H4,MAX(INDE X((A1:H4<"")*ROW(A1:H4),,)),)),"-")

It will return the last numeric value from top to bottom, left to right.

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
I want to write a function that displays the last value populated ina
range.
To help explain please see below:

column A B C D E F G
H
row
1 67 55 42 66 49 39 72
56
2 56 38 44 77 59
3
4

This is what I'm trying now, doesn't work.
=IF(COUNT(a1:h4),LOOKUP(99^99,a1:h4),"-")

I want to be able to display in this case the value 59.


I need to write a function that evaluates A1:H4 and displays the last
value
populated. In this case 59.





--
Sony Luvy

Explain *exactly* how the ranges are filled. By that I mean:

Is one range filled contiguously then the next range is filled and then the
next ?

Or, do the ranges hold random amounts of data?

If the ranges hold random amounts of data is the data contiguous in the
range?

If the ranges hold random amounts of data which is the last number entered,
from left to right or from top to bottom? If it's left to right then row 57
has the last number. If it's top to bottom then row 71 has the last number:

row 57: 1,2,3,4,5,6
row 64: 1,2,3
row 71: 1,2,3,4

Are the numbers sequential so that maybe you can just look for a max/min?

Are they dates? Can we just look for the most recent date?

Help us help you by giving us *as much information as possible*.

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
Biff, I just integrated the function below and got a #REF! error, and know
why. I didn't present the problem exactly right. The cell range is not
contiguous, so A1:h4 does not accurately reflect the cell range. The
actual
cell ranges to "lookup" a

b57:ae57,b64:ae64,b71:ae71

This non-contiguous range must be read to identify the most recent (or
last)
value populated.

Can you please modify your function below to accommodate the
non-contiguous
range above?

Thanks for your help. - Tom
--
Sony Luvy


"T. Valko" wrote:

*Maybe* this:

=IF(COUNT(A1:H4),LOOKUP(1E100,INDEX(A1:H4,MAX(INDE X((A1:H4<"")*ROW(A1:H4),,)),)),"-")

It will return the last numeric value from top to bottom, left to right.

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
I want to write a function that displays the last value populated ina
range.
To help explain please see below:

column A B C D E F G
H
row
1 67 55 42 66 49 39
72
56
2 56 38 44 77 59
3
4

This is what I'm trying now, doesn't work.
=IF(COUNT(a1:h4),LOOKUP(99^99,a1:h4),"-")

I want to be able to display in this case the value 59.


I need to write a function that evaluates A1:H4 and displays the last
value
populated. In this case 59.





--
Sony Luvy

Biff, Thanks and sorry for delayed response.

Actually each range is filled complete (on average one cell per day). And
all data entered is numbers (actually daily stock prices, ie. 25.625, etc.).

All cells in b57:ae57 first
then all cells in b64:ae64
then all cells in b71:ae:71 (it takes appx. 30 days per range).

and at any point in time, the function will extract the most recent (last)
cell populated. If b57:ae57 is filled, and b64:g64 is filled, the function
should return the value in g64.

Thanks for your patience and much appreciation. - Tom



--
Sony Luvy


"T. Valko" wrote:

Explain *exactly* how the ranges are filled. By that I mean:

Is one range filled contiguously then the next range is filled and then the
next ?

Or, do the ranges hold random amounts of data?

If the ranges hold random amounts of data is the data contiguous in the
range?

If the ranges hold random amounts of data which is the last number entered,
from left to right or from top to bottom? If it's left to right then row 57
has the last number. If it's top to bottom then row 71 has the last number:

row 57: 1,2,3,4,5,6
row 64: 1,2,3
row 71: 1,2,3,4

Are the numbers sequential so that maybe you can just look for a max/min?

Are they dates? Can we just look for the most recent date?

Help us help you by giving us *as much information as possible*.

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
Biff, I just integrated the function below and got a #REF! error, and know
why. I didn't present the problem exactly right. The cell range is not
contiguous, so A1:h4 does not accurately reflect the cell range. The
actual
cell ranges to "lookup" a

b57:ae57,b64:ae64,b71:ae71

This non-contiguous range must be read to identify the most recent (or
last)
value populated.

Can you please modify your function below to accommodate the
non-contiguous
range above?

Thanks for your help. - Tom
--
Sony Luvy


"T. Valko" wrote:

*Maybe* this:

=IF(COUNT(A1:H4),LOOKUP(1E100,INDEX(A1:H4,MAX(INDE X((A1:H4<"")*ROW(A1:H4),,)),)),"-")

It will return the last numeric value from top to bottom, left to right.

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
I want to write a function that displays the last value populated ina
range.
To help explain please see below:

column A B C D E F G
H
row
1 67 55 42 66 49 39
72
56
2 56 38 44 77 59
3
4

This is what I'm trying now, doesn't work.
=IF(COUNT(a1:h4),LOOKUP(99^99,a1:h4),"-")

I want to be able to display in this case the value 59.


I need to write a function that evaluates A1:H4 and displays the last
value
populated. In this case 59.





--
Sony Luvy

I can't think of an "elegant" way to do this but the formula below will
work:

=IF(COUNT(B57:AE57,B64:AE64,B71:AE71),LOOKUP(1E+10 0,IF(COUNT(B71:AE71),B71:AE71,IF(COUNT(B64:AE64),B 64:AE64,B57:AE57))),"")

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
Biff, Thanks and sorry for delayed response.

Actually each range is filled complete (on average one cell per day). And
all data entered is numbers (actually daily stock prices, ie. 25.625,
etc.).

All cells in b57:ae57 first
then all cells in b64:ae64
then all cells in b71:ae:71 (it takes appx. 30 days per range).

and at any point in time, the function will extract the most recent (last)
cell populated. If b57:ae57 is filled, and b64:g64 is filled, the
function
should return the value in g64.

Thanks for your patience and much appreciation. - Tom



--
Sony Luvy


"T. Valko" wrote:

Explain *exactly* how the ranges are filled. By that I mean:

Is one range filled contiguously then the next range is filled and then
the
next ?

Or, do the ranges hold random amounts of data?

If the ranges hold random amounts of data is the data contiguous in the
range?

If the ranges hold random amounts of data which is the last number
entered,
from left to right or from top to bottom? If it's left to right then row
57
has the last number. If it's top to bottom then row 71 has the last
number:

row 57: 1,2,3,4,5,6
row 64: 1,2,3
row 71: 1,2,3,4

Are the numbers sequential so that maybe you can just look for a max/min?

Are they dates? Can we just look for the most recent date?

Help us help you by giving us *as much information as possible*.

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
Biff, I just integrated the function below and got a #REF! error, and
know
why. I didn't present the problem exactly right. The cell range is
not
contiguous, so A1:h4 does not accurately reflect the cell range. The
actual
cell ranges to "lookup" a

b57:ae57,b64:ae64,b71:ae71

This non-contiguous range must be read to identify the most recent (or
last)
value populated.

Can you please modify your function below to accommodate the
non-contiguous
range above?

Thanks for your help. - Tom
--
Sony Luvy


"T. Valko" wrote:

*Maybe* this:

=IF(COUNT(A1:H4),LOOKUP(1E100,INDEX(A1:H4,MAX(INDE X((A1:H4<"")*ROW(A1:H4),,)),)),"-")

It will return the last numeric value from top to bottom, left to
right.

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
I want to write a function that displays the last value populated ina
range.
To help explain please see below:

column A B C D E F
G
H
row
1 67 55 42 66 49 39
72
56
2 56 38 44 77 59
3
4

This is what I'm trying now, doesn't work.
=IF(COUNT(a1:h4),LOOKUP(99^99,a1:h4),"-")

I want to be able to display in this case the value 59.


I need to write a function that evaluates A1:H4 and displays the
last
value
populated. In this case 59.





--
Sony Luvy