A function that returns the name of the current cell

[Guest]

CELL() function can return various data about the current cell, but not its
name. It does, however, return row and column serial numbers, so If I want to
lookup some cells in another table that contains their names, I have to use
this:
(CHAR(64+CELL("col",A1)))&CELL("row", A1)
(assuming that the column names have only 1 letter).
I suggest adding a possible value to the info_type argument of the function
CELL():
"name" - Text value containing the name of the cell, for example: C19.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...9-c7bfa4d0173b&dg=microsoft.public.excel.misc

 

[Peo Sjoblom]

What's wrong with

=CELL("address")



--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

The $ signs.
I don't think most people would use $ signs in cell names when they create a
lookup table such as:
A1 30
A2 45
B1 28
B2 32
etc.
I'd be happy to know the reasons the address is returned with $ signs, but
even then, I think that adding a possible value to the info_type is most
welcome.
PS: It may be a nice exercise to try to get with Excel functions from an
address containing $ signs to an address without $ signs...

 

[Peo Sjoblom]

This will return the relative cell reference of the cell that holds the
formula

=ADDRESS(COLUMN(),ROW(),4)

--
Regards,

Peo Sjoblom

(No private emails please)

 

[KL]

Hi yarp,

PS: It may be a nice exercise to try to get with Excel functions from an
address containing $ signs to an address without $ signs...

How about this quick and easy:

=SUBSTITUTE(CELL("address"),"$","")

Regards,
KL

 

[Sandy Mann]

Peo,

=ADDRESS(COLUMN(),ROW(),4)

In my XL97 it says to have the ROW() and COLUMN() the other way round ie

=ADDRESS(ROW(),COLUMN(),4)

Am I missing something?

--
Regards,

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Peo Sjoblom]

Ouch! No, you are correct, it would have helped if I tested the formula
first <bg>

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Tom Ogilvy]

You have your answer using address, but

=Substitute(CELL("address"),"$","")

 

[KL]

Hi Tom,

=Substitute(CELL("address"),"$","")
would always return A1

Not always A1 - it depends in which cell you confirm the formula, but
you are right having the same result in all cells is not correct. This one
should work:

=SUBSTITUTE(CELL("address",A1),"$","")

and another one although a bit more expensive:
=SUBSTITUTE(CELL("address",INDIRECT("rc",0)),"$","")

CHAR(64+CELL("col",A1)))&CELL("row", A1)
would always return A1

this one seems to work fine for me .

Regards,
KL

 

[rgarber50]

Chip Pearson's site has a vba function that will return the name of
cell reference:
http://www.cpearson.com/excel/named.htm

Here's the function:

Function ExactRangeName(Rng As Range) As String
On Error Resume Next
ExactRangeName = Rng.Name.Name
End Function

Hope this helps
Richard

 

[Guest]

Peo (and Sandy),
Thank you very much, this seems to be a very elegant solution, definitely
more than mine. However, I still think that there should be a more
comfortable solution, preferably within the CELL function.
Thanks again,
yarp.

 

[Guest]

KL,
I think you've used the function INDIRECT incorrectly, for 2 reasons:
1. The value of A1 doesn't interest us.
2. "rc" should be replaced with "R1C1" in order to refer to cell A1.
yarp.

 

[Guest]

KL,
Indeed quick and even too easy for someone who knows the function
SUBSTITUTE...
Thanks!
yarp.

 

[Guest]

Tom,
If I write the original function in A1, then when I drag or copy it to other
cells, A1 will change accordingly. That's still OK by me, but indeed a better
solution will not involve typing the cell we're in at all, and that's what I
asked for originally.
Thanks for your comment,
yarp.

 

[KL]

Hi yarp,

I would suggest that:

1) you try the formula before you judge
2) you read the Help about the function INDIRECT

1. The value of A1 doesn't interest us.

the formula
=INDIRECT("rc",0)
doesn't refer to the cell A1, but to a current cell (where the formula is)

2. "rc" should be replaced with "R1C1" in order to refer to cell A1.

"RC" stands for the curent cell's reference.

If you use a version of Excel in language other than English "RC" will have
to be translated.

Regards,
KL

 

[KL]

Hi yarp,

I would suggest that:

1) you try the formula before you judge
2) you read the Help about the function INDIRECT

1. The value of A1 doesn't interest us.

the formula
=INDIRECT("rc",0)
doesn't refer to the cell A1, but to a current cell (where the formula is)

2. "rc" should be replaced with "R1C1" in order to refer to cell A1.

"RC" stands for the curent cell's reference.

If you use a version of Excel in language other than English "RC" will have
to be translated.

Regards,
KL

 

[Guest]

Hello, KL.
I did try out your answer, but probably not thoroughly enough...
I'm sorry for the misunderstanding between us. You are right, and right now
I should be thanking you and eating my hat.
Thanks again!
yarp.

 

[KL]

Hi yarp,

Nothing to be sorry about and less to eat your hat
If my post sounded emotional/negative - I didn't mean that, was just trying
to draw your attention back to the formula ;-)

Regards,
KL