Return "cell reference"

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

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

One way, for a single cell

=ADDRESS(ROW(),COLUMN())

Regards

Trevor


"0-0 Wai Wai ^-^" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
> --
> Additional information:
> - I'm using Office XP
> - I'm using Windows XP
>
>

On Wed, 7 Sep 2005 04:36:52 +0800, "0-0 Wai Wai ^-^" <[email protected]> wrote:

>
>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 ^-^ 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

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

本人的能力非常有**. 如有不當之處, 望識者不吝賜正!!
My ability is very limited. Hope you will not mind to enlighten me if I do
wrongly.
"Trevor Shuttleworth" <[email protected]> 在郵件
news:[email protected] 中撰寫...
> 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" <[email protected]> ???
news:[email protected] ???...
> 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.

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!!
..
..

> >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

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

You are very very helpful.
Thanks so much. :P

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.

>>=MID(CELL("Address",(A1,A1:A10)),6,1024)
>>returns $A$1:$A$10
>
>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.

"Harlan Grove" <[email protected]> ???
news:[email protected] ???...
> 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.

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?

>
> >>=MID(CELL("Address",(A1,A1:A10)),6,1024)
> >>returns $A$1:$A$10
> >
> >What are the "6" and "1024"?

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 :P

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

0-0 Wai Wai ^-^ wrote...
..=2E.
>Sorry that I still don't get it.
>INDIRECT function is:
>- INDIRECT(ref_text,a1)
..=2E.
>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.

=3DINDIRECT("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 inter=
pret
>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:
=3Dreturn_cell_reference_of_that=AD_cell()
{The above is a fictitious formula only}


The answer will be A1."

If you enter

=3DSUBSTITUTE(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

=3DA1

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?

"Harlan Grove" <[email protected]> ???
news:[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. :P
..
..


============
>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.

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?

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.

0-0 Wai Wai ^-^ wrote...
>>>>=MID(CELL("Address",(A1,A1:A10)),6,1024)
>>>>returns $A$1:$A$10
....
>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,C3:D7,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,".

> >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,C3:D7,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,C34:D45)), 6, 9999)

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