Formula that returns address of a cell reference

[mansfin]

Cell A1 in Sheet1 contains a reference cell A1 on Sheet2.

Formula in A1: =Sheet2!$A$1

I want a formula in cell A2 to reference cell A1, but return the address of the cell that A1 is referencing, "Sheet2!$A$1"

Is there such a formula? Thanks!

[Olly]

In Excel 2013 and later, you can use:

Please Login or Register  to view this content.

Your profile shows you're using Excel 2010, so you can add a small UDF, then use the same formula:

Please Login or Register  to view this content.

[mansfin]

Thanks! How do I now use the formula text as the reference parameter in the OFFSET function?

[mansfin]

Figured it out. I just need to remove the equals sign in the formula text and use the INDIRECT function.

=OFFSET(INDIRECT(RIGHT(FORMULATEXT(A1),LEN(FORMULATEXT(A1))-1)),1,0)