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

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

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

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

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

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

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

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