Need Last Row Name

[premjeet]

Please provide formula to find last non blank row name.

 

[DaveTurton]

=COUNTA(A:A) will give you the total number of non blank rows in Column A. Is this the sort of thing you are after?

 

[Debraj]

Hi Premjeet,


Try below too..

=MATCH(REPT("z",255),A:A)

Regards,

Deb

 

[premjeet]

Hi DaveT.


Thank for quick response but that is not what i am looking for.


As example i have a data

[pre]

A B C D E   Result Should be
1 2 1 3 1   E1
1 1 2       C2
1 2 3 4     D3

[/pre]
Hope you understand..

 

[bobhc]

Good day premjeet


Not sure what you mean by row name, I have assumed you mean number!


Both formulas need to be array-entered, meaning that you press Ctrl-Shft-Enter after you type or paste it in. The below is for column A: The formula is in B1


=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))

This will give you the value in the last cell in the column


=MAX(ROW(A:A)*(A:A<>""))

This will give you the last row number in the column


Both work with alpha, Numerical and blanks and update as you enter new data.


If you put =CELL(address) in to a cell near to your data so that you can see without scrolling then it will all ways display the last cell that data was entered into.

 

[premjeet]

Thanks Bobhc,


Your array formula works very well for me.


But i need last Non blank cell address.

 

[bobhc]

Hi premjeet


I think we are at a miss-understanding with "cell address" "row name" but try this it may be of help.


=ADDRESS(LOOKUP(2,1/(A:A<>""),ROW(A:A)),1)


It work with alpha, Numerical and blanks and update as you enter new data.


Take a look at the uplaod


https://dl.dropboxusercontent.com/u/75495784/Last%20cell%20in%20Column.xlsx

 

[premjeet]

Thanks Bobhc.