Last Line

[terri]

Is there any formula to tell me what the last line of data in a column is?
-- for example if data ends in cell A45 -- is there a formula that would look
a column a and return A45?

 

[Domenic]

Is there any formula to tell me what the last line of data in a column is?
-- for example if data ends in cell A45 -- is there a formula that would look
a column a and return A45?

Do you want to return the contents of A45 or the cell reference A45?

Does Column A contain text or numerical values? Or does it contain both?

 

[terri]

I want it to return the cell reference of A45. The column contains
alpha-numerics.

 

[Domenic]

I want it to return the cell reference of A45. The column contains
alpha-numerics.

Alpha-numeric values, such as ABC12345, are considered text values. As
such, the following formulas will find the last text value in Column A
and return its cell reference...

=CELL("address",INDEX(A:A,MATCH(REPT("z",255),A:A)))

or

=SUBSTITUTE(CELL("address",INDEX(A:A,MATCH(REPT("z",255),A:A))),"$","")

 

[Gord Dibben]

=ADDRESS(MATCH(REPT("z",255),A:A),1) will return address of last text data
in column A

=ADDRESS(MATCH(99^99,A:A),1) will return address of last numeric data.

The ,1 is a variable for cell address reference style.

1 is absolute................4 is relative........2 and 3 are row and
column relative.


Gord Dibben MS Excel MVP