Have cell display the right-most non-blank cell?

J

Jay

Sorry about the turgid Subject line :)

What I mean is: How do I have a cell display the contents of the last
non-blank cell within a range (just one row). And what I mean by 'last'
is the one furthest to the right.

For example, let's say the range is A1:M1

What can I have in cell N1 that will copy the contents of the last
non-blank cell in the range, reading left to right. So if cells A1 to J1
contained data, but K1:M1 were blank, J1 would be the last non-blank
cell (left to right).Alternatively, if this cell was cleared, I1 would
qualify as the last non-blank cell, so I would want the contents of this
cell displayed in N1.

Any help greatly appreciated.

Many thanks,

Jay
 
K

Ken Johnson

Jay said:
Sorry about the turgid Subject line :)

What I mean is: How do I have a cell display the contents of the last
non-blank cell within a range (just one row). And what I mean by 'last'
is the one furthest to the right.

For example, let's say the range is A1:M1

What can I have in cell N1 that will copy the contents of the last
non-blank cell in the range, reading left to right. So if cells A1 to J1
contained data, but K1:M1 were blank, J1 would be the last non-blank
cell (left to right).Alternatively, if this cell was cleared, I1 would
qualify as the last non-blank cell, so I would want the contents of this
cell displayed in N1.

Any help greatly appreciated.

Many thanks,
Jay
Click to expand...

Hi Jay,

If there are no blanks between A1 and the rightmost nonblank cell in
the range A1:M1, then you could use...

=INDIRECT(ADDRESS(ROW(),COUNTA($A1:$M1)))

Ken Johnson
 
P

Pete_UK

Here's an alternative, assuming your data is numeric:

=LOOKUP(999999,A1:M1)

The first number must be larger than any numbers you expect in your
data - you could make it 9.99999999999999E307 if you really want to.

Hope this helps.

Pete
 
R

RagDyeR

And if your data can be *either* text or numeric:

=LOOKUP(2,1/(A1:M1<>""),A1:M1)

This is assuming that there are *no* formulas in the range that *might*
equate to null ( "" ),
since this formula will *not* return a last null filled cell.

If such formulas *do* exist in the range, try this:

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

--

HTH,

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

Here's an alternative, assuming your data is numeric:

=LOOKUP(999999,A1:M1)

The first number must be larger than any numbers you expect in your
data - you could make it 9.99999999999999E307 if you really want to.

Hope this helps.

Pete
 
J

Jay

Cheers Pete,

I'm being a bit stupid but can't figure out how the LOOKUP works. It
was my understanding that the function would look for the large value
(999999) and return the next highest in the range. But it doesn't - it
returns the right-most value in the range? (which of course is what I
want it to do).

Would you mind explaining how it works as I'm having a major-eejit moment.

Thanks,

Jason
 
D

daddylonglegs

This really exploits an "oddity" in Excel.

If the lookup value cannot be found (which it won't be if you pick
big enough number) then LOOKUP should return the largest value in th
range - *but* LOOKUP works on the assumption that the range is sorte
ascending so the last numeric value is *assumed* to be the largest an
therefore it is that value which is returned.

Of course, in reality that may not be the largest value because you
range may not be sorted....
 
Top