• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find cell address of last cell with data in column

W

westend9876

Member
I want to find the cell address of the last cell with data in a column and then refer to that cell in a formula.

So, for example, Column A has values in A2 - A20. I want a formula in B2 that returns "A20" since this is the last cell that contains data. Then in C2 I want to use "A20" in a formula so that it refers to the actual value in cell A20. Thanks.
 
Hi, westend9876!
Try this:
=DIRECCION(COINCIDIR(BUSCAR(CARACTER(255);A:A);A:A;1);COLUMNA(A$1);4;1) -----> in english: =ADDRESS(MATCH(FIND(CHAR(255),A:A),A:A,1),COLUMN(A$1),4,1)
Regards!
 
Hi ,

Try this :

=ADDRESS(MATCH("zzzzzzzzzzzzzzz",A:A),1)

This will work if the data in column A is text ; if it numeric , replace the "zzzzzzzzzzzzzzz" by a high enough number , say 9^9 , as follows :

=ADDRESS(MATCH(9^9,A:A),1)

Narayan
 
Hi Narayan. I moved the data to Column C and used the same formula simply replacing A:A with C:C, =ADDRESS(MATCH(9^9,C:C),1) and the value returned is still $A$20. In fact, this value is returned regardless of what column the data is in. How do I get the value to reflect the actual column?
 
Hi West,

How about this for B2:
Code: 
="A"&COUNTA(A:A)+1

And either this for C2:
Code: 
=INDIRECT("A"&COUNTA(A:A)+1)
Code: 
=INDIRECT(B2)
 
Alright, here is another solution :)

Code: 
="A"&MAX(IF(LEN(A:A),ROW(A:A)))
enter as array (ctrl + shift + enter)
 
You must log in or register to reply here.
Back
Top