What function or macro would I use to return the last list information in a
given range of row even when some cells are left blank?

One way courtesy of Harlan Grove

=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)

if you are looking horizontal just change to

=LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)

--
Regards,

Peo Sjoblom


"BGarcia" wrote in message
...
What function or macro would I use to return the last list information in
a
given range of row even when some cells are left blank?

Leave it to Harlan to trump all the old standards.<g

And since ISBLANK accepts total references,

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Peo Sjoblom" wrote in message
...
One way courtesy of Harlan Grove

=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)

if you are looking horizontal just change to

=LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)

--
Regards,

Peo Sjoblom


"BGarcia" wrote in message
...
What function or macro would I use to return the last list information
in
a
given range of row even when some cells are left blank?

Hi RD

It appears that this formula will work on an entire row but not an entire
column.

I put a number in IV1 and used:

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)

Which worked.

I put a number in A65536 and used:

=LOOKUP(2,1/(1-ISBLANK(A:A)),A:A)

This did not work and returned 0.

If I change the ref in ISBLANK to A1:A65535 or A2:A65536 it then worked.

Biff

"Ragdyer" wrote in message
...
Leave it to Harlan to trump all the old standards.<g

And since ISBLANK accepts total references,

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Peo Sjoblom" wrote in message
...
One way courtesy of Harlan Grove

=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)

if you are looking horizontal just change to

=LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)

--
Regards,

Peo Sjoblom


"BGarcia" wrote in message
...
What function or macro would I use to return the last list information
in
a
given range of row even when some cells are left blank?

I noticed that also.

Maybe Harlan can explain?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Biff" wrote in message
...
Hi RD

It appears that this formula will work on an entire row but not an entire
column.

I put a number in IV1 and used:

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)

Which worked.

I put a number in A65536 and used:

=LOOKUP(2,1/(1-ISBLANK(A:A)),A:A)

This did not work and returned 0.

If I change the ref in ISBLANK to A1:A65535 or A2:A65536 it then worked.

Biff

"Ragdyer" wrote in message
...
Leave it to Harlan to trump all the old standards.<g

And since ISBLANK accepts total references,

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"Peo Sjoblom" wrote in message
...
One way courtesy of Harlan Grove

=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)

if you are looking horizontal just change to

=LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)

--
Regards,

Peo Sjoblom


"BGarcia" wrote in message
...
What function or macro would I use to return the last list information
in
a
given range of row even when some cells are left blank?

Ragdyer wrote...
....
And since ISBLANK accepts total references,

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
....

Only because whole row references are no big deal - only 256 entries.
Whole column references still fubar.

It has to be more complex then just a "number of cells (entries)" issue
Harlan, since A1:A65536 DOES work!
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Harlan Grove" wrote in message
ups.com...
Ragdyer wrote...
....
And since ISBLANK accepts total references,

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
....

Only because whole row references are no big deal - only 256 entries.
Whole column references still fubar.

RagDyeR wrote...
It has to be more complex then just a "number of cells (entries)"
issue
Harlan, since A1:A65536 DOES work!
....

Not on my system. First, Excel *ALWAYS* replaces A1:A65536 with A:A.
Second, in a new worksheet, the following formula entered in B1

B1:
=SUMPRODUCT(--ISBLANK(A:A))

returns 0 rather than 65536. When I change the formula to

B1:
=SUMPRODUCT(--ISBLANK(A1:A65535))

it returns 65535. What results do you get on your system?

As for the formula in this thread, . . .

Ragdyer wrote...
...
And since ISBLANK accepts total references,

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
....

with A:A still all blank, I enter the following formula in B2.

B2:
=LOOKUP(2,1/(1-ISBLANK(A:A)),ROW(A:A))

It returns 1 to start with, not #N/A and not 0. If I change it to

B2:
=LOOKUP(2,1/(1-ISBLANK(A1:A65535)),ROW(A1:A65535))

it returns #N/A as expected (at least as I expect).

Reverting to the A:A formula, I then enter 9 in cell A11. The formula
*STILL* returns 1, not 11. What results do you get on your system?

Using Row1,

For *numeric* data *only*:
=LOOKUP(9.99999999999999E+307,1:1)

For *text* data *only*:
=LOOKUP(REPT("z",255),1:1)

For text or numbers:
=INDEX(1:1,MAX(MATCH({"zzzzzzz",9.9999999E+307},1: 1)))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"BGarcia" wrote in message
...
What function or macro would I use to return the last list information in
a
given range of row even when some cells are left blank?

Raddyer,

This works perfect.....Thanks a bunch!



"Ragdyer" wrote:

Using Row1,

For *numeric* data *only*:
=LOOKUP(9.99999999999999E+307,1:1)

For *text* data *only*:
=LOOKUP(REPT("z",255),1:1)

For text or numbers:
=INDEX(1:1,MAX(MATCH({"zzzzzzz",9.9999999E+307},1: 1)))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"BGarcia" wrote in message
...
What function or macro would I use to return the last list information in
a
given range of row even when some cells are left blank?

Appreciate the feed-back ... BUT ... don't you think Peo's suggestion is
more concise?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"BGarcia" wrote in message
...
Raddyer,

This works perfect.....Thanks a bunch!



"Ragdyer" wrote:

Using Row1,

For *numeric* data *only*:
=LOOKUP(9.99999999999999E+307,1:1)

For *text* data *only*:
=LOOKUP(REPT("z",255),1:1)

For text or numbers:
=INDEX(1:1,MAX(MATCH({"zzzzzzz",9.9999999E+307},1: 1)))
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-


"BGarcia" wrote in message
...
What function or macro would I use to return the last list information
in
a
given range of row even when some cells are left blank?