A function that returns the name of the current cell
 

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/comm...lic.excel.misc

What's wrong with

=CELL("address")



--
Regards,

Peo Sjoblom

(No private emails please)


"yarp" wrote in message
...
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/comm...lic.excel.misc

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" wrote:

What's wrong with

=CELL("address")



--
Regards,

Peo Sjoblom

(No private emails please)


"yarp" wrote in message
...
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/comm...lic.excel.misc

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)


"yarp" wrote in message
...
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" wrote:

What's wrong with

=CELL("address")



--
Regards,

Peo Sjoblom

(No private emails please)


"yarp" wrote in message
...
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/comm...lic.excel.misc

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

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

Replace@mailinator with @tiscali.co.uk

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

--
Regards,

Peo Sjoblom

(No private emails please)


"Sandy Mann" wrote in message
...
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

Replace@mailinator with @tiscali.co.uk

You have your answer using address, but

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

------------------

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

would always return A1
--
Regards,
Tom Ogilvy

"yarp" wrote in message
...
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" wrote:

What's wrong with

=CELL("address")



--
Regards,

Peo Sjoblom

(No private emails please)


"yarp" wrote in message
...
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/comm...lic.excel.misc

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

Chip Pearson's site has a vba function that will return the name of a
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



yarp Wrote:
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://tinyurl.com/dnjj8


--
rgarber50
------------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350
View this thread: http://www.excelforum.com/showthread...hreadid=393702

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.

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom

(No private emails please)


"Sandy Mann" wrote in message
...
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

Replace@mailinator with @tiscali.co.uk

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.

"KL" wrote:

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

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

"KL" wrote:

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

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.

"Tom Ogilvy" wrote:

You have your answer using address, but

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

------------------

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

would always return A1
--
Regards,
Tom Ogilvy

"yarp" wrote in message
...
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" wrote:

What's wrong with

=CELL("address")



--
Regards,

Peo Sjoblom

(No private emails please)


"yarp" wrote in message
...
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/comm...lic.excel.misc

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

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

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" wrote:

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

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


"yarp" wrote in message
...
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" wrote:

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