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

Biff, thanks man, that worked great, much appreciated - Tom
--
Sony Luvy


"T. Valko" wrote:

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
Biff, thanks man, that worked great, much appreciated - Tom
--
Sony Luvy


"T. Valko" wrote:

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

Biff, I got an error on this function (it just says worksheet function
contains error). And the word COUNT the last time it's displayed below is
highlighted. The function worked until I increased the lookup range to
include b92:ae92 (through b86:ae86 worked). I'm not sure why the error
exists. Please help. And the non-contiguous ranges I'm searching for the
last number displayed are also below.

=IF(COUNT($B$56:$AE$56,$B$62:$AE$62,$B$68:$AE$68,$ B$74:$AE$74,$B$80:$AE$80,$B$86:$AE$86,$B$92:$AE$92 ),LOOKUP(1E+100,if(count($B$92:$AE$92),$B$92:$AE$9 2,IF(COUNT($B$86:$AE$86),$B$86:$AE$86,IF(COUNT($B$ 80:$AE$80),$B$80:$AE$80,IF(COUNT($B$74:$AE$74),$B$ 74:$AE$74,IF(COUNT($B$68:$AE$68),$B$68:$AE$68,IF(C OUNT($B$62:$AE$62),$B$62:$AE$62,$B$56:$AE$56)))))) ),"")

b56:ae56
b62:ae62
b68:ae68
b74:ae74
b80:ar80
b86:ae86
b92:ae92

Thanks for your review and advice.
--
Sony Luvy


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
Biff, thanks man, that worked great, much appreciated - Tom
--
Sony Luvy


"T. Valko" wrote:

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

You've exceeded the maximum nesting level for functions which is 7.

Each row range starts in column B. Is there some kind of descriptive row
header for each of these ranges in column A? Like maybe a date?

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
Biff, I got an error on this function (it just says worksheet function
contains error). And the word COUNT the last time it's displayed below is
highlighted. The function worked until I increased the lookup range to
include b92:ae92 (through b86:ae86 worked). I'm not sure why the error
exists. Please help. And the non-contiguous ranges I'm searching for the
last number displayed are also below.

=IF(COUNT($B$56:$AE$56,$B$62:$AE$62,$B$68:$AE$68,$ B$74:$AE$74,$B$80:$AE$80,$B$86:$AE$86,$B$92:$AE$92 ),LOOKUP(1E+100,if(count($B$92:$AE$92),$B$92:$AE$9 2,IF(COUNT($B$86:$AE$86),$B$86:$AE$86,IF(COUNT($B$ 80:$AE$80),$B$80:$AE$80,IF(COUNT($B$74:$AE$74),$B$ 74:$AE$74,IF(COUNT($B$68:$AE$68),$B$68:$AE$68,IF(C OUNT($B$62:$AE$62),$B$62:$AE$62,$B$56:$AE$56)))))) ),"")

b56:ae56
b62:ae62
b68:ae68
b74:ae74
b80:ar80
b86:ae86
b92:ae92

Thanks for your review and advice.
--
Sony Luvy


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"sony654" wrote in message
...
Biff, thanks man, that worked great, much appreciated - Tom
--
Sony Luvy


"T. Valko" wrote:

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

First define the following....

Insert Name Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=LOOKUP(BigNum,CHOOSE(MATCH(TRUE,COUNTIF(INDIRECT( {"B92:AE92","B86:AE86",
"B80:AE80","B74:AE74","B68:AE68","B62:AE62","B56:A E56"}),"<")0,0),B92:A
E92,B86:AE86,B80:AE80,B74:AE74,B68:AE68,B62:AE62,B 56:AE56))

Hope this helps!

In article ,
sony654 wrote:

Biff, I got an error on this function (it just says worksheet function
contains error). And the word COUNT the last time it's displayed below is
highlighted. The function worked until I increased the lookup range to
include b92:ae92 (through b86:ae86 worked). I'm not sure why the error
exists. Please help. And the non-contiguous ranges I'm searching for the
last number displayed are also below.

=IF(COUNT($B$56:$AE$56,$B$62:$AE$62,$B$68:$AE$68,$ B$74:$AE$74,$B$80:$AE$80,$B$
86:$AE$86,$B$92:$AE$92),LOOKUP(1E+100,if(count($B$ 92:$AE$92),$B$92:$AE$92,IF(C
OUNT($B$86:$AE$86),$B$86:$AE$86,IF(COUNT($B$80:$AE $80),$B$80:$AE$80,IF(COUNT($
B$74:$AE$74),$B$74:$AE$74,IF(COUNT($B$68:$AE$68),$ B$68:$AE$68,IF(COUNT($B$62:$
AE$62),$B$62:$AE$62,$B$56:$AE$56))))))),"")

b56:ae56
b62:ae62
b68:ae68
b74:ae74
b80:ar80
b86:ae86
b92:ae92

Thanks for your review and advice.
--
Sony Luvy

This may be the "easiest" way to do this but requires the free add-in
Morefunc.xll from:

http://xcell05.free.fr/morefunc/english/index.htm

Using named ranges for:

b56:ae56 = rng1
b62:ae62 = rng2
b68:ae68 = rng3
b74:ae74 = rng4
b80:ae80 = rng5
b86:ae86 = rng6
b92:ae92 = rng7

This formula can refer to up to 29 ranges.

=LOOKUP(1E100,ARRAY.JOIN(rng1,rng2,rng3,rng4,rng5, rng6,rng7))

The ARRAY.JOIN function creates a one dimensional vertical array from the
referenced ranges. The total size of this vertical array can not exced 65535
items.


--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
First define the following....

Insert Name Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=LOOKUP(BigNum,CHOOSE(MATCH(TRUE,COUNTIF(INDIRECT( {"B92:AE92","B86:AE86",
"B80:AE80","B74:AE74","B68:AE68","B62:AE62","B56:A E56"}),"<")0,0),B92:A
E92,B86:AE86,B80:AE80,B74:AE74,B68:AE68,B62:AE62,B 56:AE56))

Hope this helps!

In article ,
sony654 wrote:

Biff, I got an error on this function (it just says worksheet function
contains error). And the word COUNT the last time it's displayed below
is
highlighted. The function worked until I increased the lookup range to
include b92:ae92 (through b86:ae86 worked). I'm not sure why the error
exists. Please help. And the non-contiguous ranges I'm searching for
the
last number displayed are also below.

=IF(COUNT($B$56:$AE$56,$B$62:$AE$62,$B$68:$AE$68,$ B$74:$AE$74,$B$80:$AE$80,$B$
86:$AE$86,$B$92:$AE$92),LOOKUP(1E+100,if(count($B$ 92:$AE$92),$B$92:$AE$92,IF(C
OUNT($B$86:$AE$86),$B$86:$AE$86,IF(COUNT($B$80:$AE $80),$B$80:$AE$80,IF(COUNT($
B$74:$AE$74),$B$74:$AE$74,IF(COUNT($B$68:$AE$68),$ B$68:$AE$68,IF(COUNT($B$62:$
AE$62),$B$62:$AE$62,$B$56:$AE$56))))))),"")

b56:ae56
b62:ae62
b68:ae68
b74:ae74
b80:ar80
b86:ae86
b92:ae92

Thanks for your review and advice.
--
Sony Luvy

Biff, Thanks, I have been away from my worksheet development for awhile. If
I add-in the function below will it reside in the file permanently?

Thanks
--
Sony Luvy


"T. Valko" wrote:

This may be the "easiest" way to do this but requires the free add-in
Morefunc.xll from:

http://xcell05.free.fr/morefunc/english/index.htm

Using named ranges for:

b56:ae56 = rng1
b62:ae62 = rng2
b68:ae68 = rng3
b74:ae74 = rng4
b80:ae80 = rng5
b86:ae86 = rng6
b92:ae92 = rng7

This formula can refer to up to 29 ranges.

=LOOKUP(1E100,ARRAY.JOIN(rng1,rng2,rng3,rng4,rng5, rng6,rng7))

The ARRAY.JOIN function creates a one dimensional vertical array from the
referenced ranges. The total size of this vertical array can not exced 65535
items.


--
Biff
Microsoft Excel MVP


"Domenic" wrote in message
...
First define the following....

Insert Name Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=LOOKUP(BigNum,CHOOSE(MATCH(TRUE,COUNTIF(INDIRECT( {"B92:AE92","B86:AE86",
"B80:AE80","B74:AE74","B68:AE68","B62:AE62","B56:A E56"}),"<")0,0),B92:A
E92,B86:AE86,B80:AE80,B74:AE74,B68:AE68,B62:AE62,B 56:AE56))

Hope this helps!

In article ,
sony654 wrote:

Biff, I got an error on this function (it just says worksheet function
contains error). And the word COUNT the last time it's displayed below
is
highlighted. The function worked until I increased the lookup range to
include b92:ae92 (through b86:ae86 worked). I'm not sure why the error
exists. Please help. And the non-contiguous ranges I'm searching for
the
last number displayed are also below.

=IF(COUNT($B$56:$AE$56,$B$62:$AE$62,$B$68:$AE$68,$ B$74:$AE$74,$B$80:$AE$80,$B$
86:$AE$86,$B$92:$AE$92),LOOKUP(1E+100,if(count($B$ 92:$AE$92),$B$92:$AE$92,IF(C
OUNT($B$86:$AE$86),$B$86:$AE$86,IF(COUNT($B$80:$AE $80),$B$80:$AE$80,IF(COUNT($
B$74:$AE$74),$B$74:$AE$74,IF(COUNT($B$68:$AE$68),$ B$68:$AE$68,IF(COUNT($B$62:$
AE$62),$B$62:$AE$62,$B$56:$AE$56))))))),"")

b56:ae56
b62:ae62
b68:ae68
b74:ae74
b80:ar80
b86:ae86
b92:ae92

Thanks for your review and advice.
--
Sony Luvy

Domenic, Thanks. I have formula added and it seems to be woking
--
Sony Luvy


"Domenic" wrote:

First define the following....

Insert Name Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=LOOKUP(BigNum,CHOOSE(MATCH(TRUE,COUNTIF(INDIRECT( {"B92:AE92","B86:AE86",
"B80:AE80","B74:AE74","B68:AE68","B62:AE62","B56:A E56"}),"<")0,0),B92:A
E92,B86:AE86,B80:AE80,B74:AE74,B68:AE68,B62:AE62,B 56:AE56))

Hope this helps!

In article ,
sony654 wrote:

Biff, I got an error on this function (it just says worksheet function
contains error). And the word COUNT the last time it's displayed below is
highlighted. The function worked until I increased the lookup range to
include b92:ae92 (through b86:ae86 worked). I'm not sure why the error
exists. Please help. And the non-contiguous ranges I'm searching for the
last number displayed are also below.

=IF(COUNT($B$56:$AE$56,$B$62:$AE$62,$B$68:$AE$68,$ B$74:$AE$74,$B$80:$AE$80,$B$
86:$AE$86,$B$92:$AE$92),LOOKUP(1E+100,if(count($B$ 92:$AE$92),$B$92:$AE$92,IF(C
OUNT($B$86:$AE$86),$B$86:$AE$86,IF(COUNT($B$80:$AE $80),$B$80:$AE$80,IF(COUNT($
B$74:$AE$74),$B$74:$AE$74,IF(COUNT($B$68:$AE$68),$ B$68:$AE$68,IF(COUNT($B$62:$
AE$62),$B$62:$AE$62,$B$56:$AE$56))))))),"")

b56:ae56
b62:ae62
b68:ae68
b74:ae74
b80:ar80
b86:ae86
b92:ae92

Thanks for your review and advice.
--
Sony Luvy