Display cell address

boxsterman

Active Member
Joined
Apr 16, 2002
Messages
279
Hi all,

If I am in cell A1, how do I select cell C1 and have it displayed in the current cell. I don't want the value of the cell, I want the actual cell address - the value I want to show in cell A1 is the actual address "C1".

Thanks,

J
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
=SUBSTITUTE(CELL("address",$C$1),"$",)

which replaces the $ in the resulting text with nothing
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

=ADDRESS(1,3,4)
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Nice solution, one in my haste I forgot about..

for clarification...

ADDRESS(row#, col#,abs)

Abs = reference returned 1 = absolute, 2 absolute row/relative column, 3 = absolute column, relative row , 4 = relative

a relative reference is without any $

an absolute reference is anchored by the $

to see the row and column #s go to TOOLS>OPTIONS>GENERAL and check the R1C1 check box... and the letters will show their numerical equivalent.

A1 reference style is Column-Letter, R1C1 reference is Row-Column reference..
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

It's in Xecel Help

refer Address function...
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Of course it is, should I not have posted clarification for the OP?

If one was a not familiar enough with excel to know the answer to the question, I would not have known what the 1,3,4 meant in the formula....didn't mean to offend you or anything, just trying to be helpful to the OP, as you were, by explaining the syntax in your answer for the OP's benefit.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
If one was a not familiar enough with excel to know the answer to the question, I would not have known what the 1,3,4 meant in the formula

It is nice to have such, but OP should look at Help before he ask again. I think?
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
True! But if he had, the question would not have been posed to begin with...anyways...I learn alot here, glad someone picked up my slack nonetheless
 

Watch MrExcel Video

Forum statistics

Threads
1,118,821
Messages
5,574,515
Members
412,599
Latest member
Schu94
Top