On Sat, 19 Apr 2008 21:26:00 -0700, yshridhar
wrote:

=LOOKUP(255,A2:Z2) - for numeric value
=LOOKUP(REPT("z",255),A2:Z2) - for text value
best wishes
Sreedhar
"Koffiepit" wrote:

Does anybody know how to construct a worksheet function that displays the
location, or better still, the value, of the last non-blank cell in a large
range like the second row in a spreadsheet for instance?

O yes, and if you do know, could you explain how it works?

Much appreciated in advance.

Koffiepit


Assuming "the last non-blank cell" is defined as the rightmost
non-blank cell in the last row of the range that has any non-blank
cell you can try this to get the value og the last non-blank cell:

=OFFSET(A1,INT(MAX(NOT(ISBLANK(myrange))*(COLUMNS( 1:1)*ROW(myrange)+COLUMN(myrange)))/COLUMNS(1:1))-1,
MOD(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(my range)+COLUMN(myrange)))/COLUMNS(1:1),1)*COLUMNS(1:1)-1)

myrange is the range in question.
This should be entered, everything on one line, as an array formula,
i.e. by pressing CTRL+SHIFT+ENTER rather than just ENTER

This is how it works:

The ISBLANK(myrange) part gives an array with TRUE for all blank cells
and FALSE for all non-blank cell

The COLUMNS(1:1)*ROW(myrange)+COLUMN(myrange) part gives an array
with a "cell number" for each cell in the range. The number
COLUMNS(1:1) part is there to make the number unique for all cells.

When these two arrays are multiplied you get an array with zeroes for
the blank cells and the "cell number" for the non-blank cells.

MAX then finds the maximum "cell number" for a non-blank cell.

The INT and MOD parts are there to transform the cell number to row
and column for the cell.

INT(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(my range)+COLUMN(myrange)))/COLUMNS(1:1))
gives the row number (r) of the cell

MOD(MAX(NOT(ISBLANK(myrange))*(COLUMNS(1:1)*ROW(my range)+COLUMN(myrange)))/COLUMNS(1:1),1)*COLUMNS(1:1)
gives the column number (c) of the cell

and OFFSET(A1,r-1,c-1) finally gives the value of the cell with row
number r and column number c

Hope this helps / Lars-Åke