Return "cell reference"

[0-0 Wai Wai ^-^]

Hi.
How to do the following:
1) Return the cell reference of the cell
eg:
Type this formula in cell A1:
=return_cell_reference_of_that_cell()
{The above is a fictitious formula only}

The answer will be A1.
Preferably, it is great for me to decide on how the reference is displayed, eg:
- absolute (ie $A$1) or
- column-absolute (ie $A1) or
- row-absolute (ie A$1)
- relative (ie A1)

2) Return cell reference(s) of the target
eg:
Type this formula in cell B1:
=return_cell_reference(target_cell)
{The above is a fictitious formula only}

eg:
=return_cell_reference(A3)
Answer: A3

=return_cell_reference(A1:A10)
answer: A1:A10

=return_cell_reference(A1,A3,A5)
answer: A1,A3,A5

Again preferably, it is great for me to decide on how the reference is
displayed.
Thanks for your help

 

[Trevor Shuttleworth]

One way, for a single cell

=ADDRESS(ROW(),COLUMN())

Regards

Trevor

 

[Ron Rosenfeld]

Hi.
How to do the following:
1) Return the cell reference of the cell
eg:
Type this formula in cell A1:
=return_cell_reference_of_that_cell()
{The above is a fictitious formula only}

The answer will be A1.
Preferably, it is great for me to decide on how the reference is displayed, eg:
- absolute (ie $A$1) or
- column-absolute (ie $A1) or
- row-absolute (ie A$1)
- relative (ie A1)

=ADDRESS(ROW(),COLUMN())
A third, optional argument will determine how the reference is
displayed. See HELP for ADDRESS

2) Return cell reference(s) of the target
eg:
Type this formula in cell B1:
=return_cell_reference(target_cell)
{The above is a fictitious formula only}

eg:
=return_cell_reference(A3)
Answer: A3

=ADDRESS(ROW(A3),COLUMN(A3))

or, if you have A3 in D1, you could use:

=ADDRESS(ROW(INDIRECT(D1)),COLUMN(INDIRECT(D1)))

=return_cell_reference(A1:A10)
answer: A1:A10

Same principal as above, except you would need to concatenate the string with
the ":" by selecting the first and last references.

=return_cell_reference(A1,A3,A5)
answer: A1,A3,A5

Again preferably, it is great for me to decide on how the reference is
displayed.
Thanks for your help

--ron

 

[0-0 Wai Wai ^-^]

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

¥»¤Hªº¯à¤O«D±`¦³­­. ¦p¦³¤£·í¤§³B, ±æÃѪ̤£§[½ç¥¿!!
My ability is very limited. Hope you will not mind to enlighten me if I do
wrongly.

One way, for a single cell

=ADDRESS(ROW(),COLUMN())

Thanks so much!
How about if I wish to call cell reference for a target cell?

 

[Harlan Grove]

0-0 Wai Wai ^-^ wrote...

1) Return the cell reference of the cell
eg:
Type this formula in cell A1:
=return_cell_reference_of_that_cell()
{The above is a fictitious formula only}

=CELL("Address",INDIRECT("RC",0))

returns $A$1.

=SUBSTITUTE(CELL("Address",INDIRECT("RC",0)),"$","",1)

returns A$1

=SUBSTITUTE(CELL("Address",INDIRECT("RC",0)),"$","",2)

returns $A1

=SUBSTITUTE(CELL("Address",INDIRECT("RC",0)),"$","")

returns A1

2) Return cell reference(s) of the target ....
=return_cell_reference(A3)
Answer: A3

=CELL("Address",A3)

returns $A$3

=return_cell_reference(A1:A10)
answer: A1:A10

More complicated,

=MID(CELL("Address",(A1,A1:A10)),6,1024)

returns $A$1:$A$10

=return_cell_reference(A1,A3,A5)
answer: A1,A3,A5

=CELL("Address",(A1,A3,A5))

returns $A$1,$A$3,$A$5

 

[0-0 Wai Wai ^-^]

0-0 Wai Wai ^-^ wrote...

=CELL("Address",INDIRECT("RC",0))
returns $A$1.

What are "RC" and the "0"?
Why is this formula work?
I can't find this documented in my HELP file.
..
..

=SUBSTITUTE(CELL("Address",INDIRECT("RC",0)),"$","",1)
returns A$1

=SUBSTITUTE(CELL("Address",INDIRECT("RC",0)),"$","",2)
returns $A1

=SUBSTITUTE(CELL("Address",INDIRECT("RC",0)),"$","")
returns A1

Wow! Awesome!!
..
..

=CELL("Address",A3)

returns $A$3


More complicated,

=MID(CELL("Address",(A1,A1:A10)),6,1024)
returns $A$1:$A$10

What are the "6" and "1024"?
Why is this formula work?

You are very very helpful.
Thanks so much.

 

[Harlan Grove]

0-0 Wai Wai ^-^ wrote...
....

What are "RC" and the "0"?
Why is this formula work?
I can't find this documented in my HELP file.

....

INDIRECT takes two arguments. Its second argument is listed as boolean
type, TRUE/FALSE. 0 is converted to FALSE, Nonzero would be converted
to TRUE. Using 0/False as 2nd argument means the first argument is
interpretted in R1C1 addressing syntax. That's documented in full in
online help, but not in the topic for the INDIRECT function.

What are the "6" and "1024"?

These are argument to the MID function. Remove the MID call and these
arguments to it and see what the result would have been.

Why is this formula work?

Evaluate it in pieces to find out.

 

[0-0 Wai Wai ^-^]

0-0 Wai Wai ^-^ wrote...
...
...

INDIRECT takes two arguments. Its second argument is listed as boolean
type, TRUE/FALSE. 0 is converted to FALSE, Nonzero would be converted
to TRUE. Using 0/False as 2nd argument means the first argument is
interpretted in R1C1 addressing syntax. That's documented in full in
online help, but not in the topic for the INDIRECT function.

Sorry that I still don't get it.
INDIRECT function is:
- INDIRECT(ref_text,a1)
eg:
A5 contains B5
B5 contains $100
For INDIRECT(A5), it returns $100

So it is used like to read the text contained in a cell.
But in your case, you simply type "RC" (& as you said, it is used to interpret
data in R1C1 addressing syntax)
However the answer is displayed in A1 style.

What's more, INDIRECT("RC",0) doesn't really work like as to interpret in R1C1.
Rather the whole blocks seem to indicate the CELL function to read the cell
itself, so that it returns its own address/reference.
How come INDIRECT("RC",0) can be interpreted in this way?

 

[0-0 Wai Wai ^-^]

Hmm...
The problem occurs when the size of the text changes (eg B100:B500)
Every time I change the reference, I need to re-calculate the start number.

I start to figure out why it is 1024.
I think it is used to set large enough so all remaining texts.
At least you should specify the reason of 1024, so save from taxing my brain

Anyway, I still can't figure out why (A1,A1:A10) is needed to make
~CELL("Address",(A1,A1:A10))~ workable.
I suppose (A1:A10) alone should work, but not.
Again I don't see this kind of expression is documented in CELL function.
Probably I must miss something. XD

 

[Harlan Grove]

0-0 Wai Wai ^-^ wrote...
....

Sorry that I still don't get it.
INDIRECT function is:
- INDIRECT(ref_text,a1) ....
So it is used like to read the text contained in a cell.

Not necessarily. The first argument to INDIRECT can be any expression
that evaluates to text in the form of a valid range reference in the
referencing syntax given by the second argument. If the second argument
is missing, it defaults to TRUE, which means A1 referencing syntax.

=INDIRECT("B"&RIGHT("0001",1))

produces a reference to range B1.

But in your case, you simply type "RC" (& as you said, it is used to interpret
data in R1C1 addressing syntax)
However the answer is displayed in A1 style.

INDIRECT and CELL are different functions. The arguments to INDIRECT
have no bearing *WHATSOEVER* on how CELL operates. CELL("Address",.)
*ALWAYS* returns its result in A1 reference style.

What's more, INDIRECT("RC",0) doesn't really work like as to interpret in R1C1.
Rather the whole blocks seem to indicate the CELL function to read the cell
itself, so that it returns its own address/reference.

Which is exactly what you requested:

"1) Return the cell reference of the cell
eg:
Type this formula in cell A1:
=return_cell_reference_of_that­_cell()
{The above is a fictitious formula only}


The answer will be A1."

If you enter

=SUBSTITUTE(CELL("Address",INDIRECT("RC",0)),"$","")

in cell A1, the result will be the text A1.

How come INDIRECT("RC",0) can be interpreted in this way?

Read online help for R1C1 referencing. What is R alone? What is C
alone? Put them together and what does it mean? If you still don't see,
enter the formula

=A1

in cell A1. You'll get a circular reference. Use Tools > Options,
General tab to change to R1C1 referencing. Now what does the formula in
cell A1 look like?

 

[0-0 Wai Wai ^-^]

"Harlan Grove" <[email protected]> ???
???...
0-0 Wai Wai ^-^ wrote...

If the second argument
is missing, it defaults to TRUE, which means A1 referencing syntax.

When we use "RC", we can't leave the second argument blank, or an error of #Ref!
occurs.
..
..


==============

What's more, INDIRECT("RC",0) doesn't really work like as to interpret in R1C1.
Rather the whole blocks seem to indicate the CELL function to read the cell
itself, so that it returns its own address/reference.

Which is exactly what you requested:

<snip>
=============

Yes, it is what I want.
..
..


============

How come INDIRECT("RC",0) can be interpreted in this way?

Read online help for R1C1 referencing. What is R alone? What is C
alone? Put them together and what does it mean? If you still don't see,
enter the formula

=A1

in cell A1. You'll get a circular reference. Use Tools > Options,
General tab to change to R1C1 referencing. Now what does the formula in
cell A1 look like?
==============

Interesting to know "RC" means the current row and column (ie the current cell).
And it seems there's no way to express this in A1 referencing syntax.

 

[0-0 Wai Wai ^-^]

As to "RC" in INDIRECT("RC",0):
- I think the double quotes are necessary when we write it in R1C1 Referencing
style. Otherwise we receive #Ref! error.

However:
=============
When you create a formula that refers to a cell, the reference to the cell will
be updated if:
(1) the cell is moved by using the Cut command to delete the cell or
(2) the cell is moved because rows or columns are inserted or deleted.
If you always want the formula to refer to the same cell regardless of whether
the row above the cell is deleted or the cell is moved, use the INDIRECT
worksheet function. For example, if you always want to refer to cell A10, use
the following syntax:
=INDIRECT("A10")
=============

Since we've already used the double quotes for R1C1 referencing style, what
symbols do we use to distinguish these 2 effects?

 

[Harlan Grove]

0-0 Wai Wai ^-^ wrote...
....

When we use "RC", we can't leave the second argument blank,
or an error of #Ref! occurs.

....

Most of the time. References like R57 or C100 are valid in both
referencing styles, but they mean much different things. However,
better to remember that if you use R1C1 references in INDIRECT,
then you MUST pass 0 or FALSE as the second argument.

Interesting to know "RC" means the current row and column
(ie the current cell). And it seems there's no way to express
this in A1 referencing syntax.

There is. Cell A1 is A1, cell F5 is F5 and cell X99 is X99. It's
rather that there's no *SINGLE* way to reference the cell holding
the formula in A1 referencing. It differs for each cell in the
same worksheet. This is the power of R1C1 referencing.

 

[Harlan Grove]

0-0 Wai Wai ^-^ wrote...

....
Hmm...
The problem occurs when the size of the text changes
(eg B100:B500) Every time I change the reference, I need to
re-calculate the start number.

....

Change it to

=MID(CELL("Address",($A$1,A1:A10)),6,1024)

Anyway, I still can't figure out why (A1,A1:A10) is needed to make
~CELL("Address",(A1,A1:A10))~ workable.

....

OK, this is undocumented. CELL("Address",Range) only returns the
address of the top-left cell in the first area of Range. However,
whether intentional or not, it returns the *FULL* range addresses of
each subsequent area. E.g.,

CELL("Address",(A1:B5,C37,E5:G9))

returns $A$1,$C$3:$D$7,$E$5:$G$9. Pass CELL a 2-area range reference
where the first area is just cell A1 and the second area is your
intended range, then discard the unwanted "$A$1,".

 

[0-0 Wai Wai ^-^]

Anyway, I still can't figure out why (A1,A1:A10) is needed to make

...

OK, this is undocumented. CELL("Address",Range) only returns the
address of the top-left cell in the first area of Range. However,
whether intentional or not, it returns the *FULL* range addresses of
each subsequent area. E.g.,

CELL("Address",(A1:B5,C37,E5:G9))

returns $A$1,$C$3:$D$7,$E$5:$G$9. Pass CELL a 2-area range reference
where the first area is just cell A1 and the second area is your
intended range, then discard the unwanted "$A$1,".

Oh I see. The "A1" is just unwanted.
So no matter what I type:
=MID(CELL("Address",($A$1,A1:A10)), 6, 9999)
=MID(CELL("Address",($A$1,B1:B10)), 6, 9999)
=MID(CELL("Address",($A$1,C3445)), 6, 9999)

I don't need to change that "A1".
So this solution is perfect
Thanks for your help.