# Function/ formula to output a cell reference

1. ## Function/ formula to output a cell reference

Hi, is there a formula that would have as it's output the cell address of the
same cell where the formula has been entered.

e.g. In cell A10 I input a formula whose output is the cell reference A10.

I'm hoping that this should be simple.

--
Creator

2. ## RE: Function/ formula to output a cell reference

so... you want cell "A10" to read "A10"? I don't think I understand your post.
--
"I''m just a simple man trying to make my way in the universe..."

"Creator" wrote:

> Hi, is there a formula that would have as it's output the cell address of the
> same cell where the formula has been entered.
>
> e.g. In cell A10 I input a formula whose output is the cell reference A10.
>
> I'm hoping that this should be simple.
>
> --
> Creator

3. ## RE: Function/ formula to output a cell reference

Vaya con Dios,
Chuck, CABGx3

"Creator" wrote:

> Hi, is there a formula that would have as it's output the cell address of the
> same cell where the formula has been entered.
>
> e.g. In cell A10 I input a formula whose output is the cell reference A10.
>
> I'm hoping that this should be simple.
>
> --
> Creator

4. ## RE: Function/ formula to output a cell reference

Can it be done without A1 appearing in the formula?
--
Creator

"CLR" wrote:

>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Creator" wrote:
>
> > Hi, is there a formula that would have as it's output the cell address of the
> > same cell where the formula has been entered.
> >
> > e.g. In cell A10 I input a formula whose output is the cell reference A10.
> >
> > I'm hoping that this should be simple.
> >
> > --
> > Creator

5. ## RE: Function/ formula to output a cell reference

Yes, if you first give A1 a RangeName.........Insert > Name > Define.....type
in say, MyCell......

then this formula, anywhere, will return \$A\$1.........

Vaya con Dios,
Chuck, CABGx3

"Creator" wrote:

>
> Can it be done without A1 appearing in the formula?
> --
> Creator
>
>
> "CLR" wrote:
>
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Creator" wrote:
> >
> > > Hi, is there a formula that would have as it's output the cell address of the
> > > same cell where the formula has been entered.
> > >
> > > e.g. In cell A10 I input a formula whose output is the cell reference A10.
> > >
> > > I'm hoping that this should be simple.
> > >
> > > --
> > > Creator

6. ## RE: Function/ formula to output a cell reference

Ok, thanks again for indulging but I don't want any reference in the formula
to the current cell address, whether by name or otherwise. I want to use this
formula over many ranges. Is this possible? It's kinda like the cell asking
itself "=who am I?"
--
Creator

"CLR" wrote:

> Yes, if you first give A1 a RangeName.........Insert > Name > Define.....type
> in say, MyCell......
>
> then this formula, anywhere, will return \$A\$1.........
>
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
>
> "Creator" wrote:
>
> > Thanks for your response.
> >
> > Can it be done without A1 appearing in the formula?
> > --
> > Creator
> >
> >
> > "CLR" wrote:
> >
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "Creator" wrote:
> > >
> > > > Hi, is there a formula that would have as it's output the cell address of the
> > > > same cell where the formula has been entered.
> > > >
> > > > e.g. In cell A10 I input a formula whose output is the cell reference A10.
> > > >
> > > > I'm hoping that this should be simple.
> > > >
> > > > --
> > > > Creator

7. ## RE: Function/ formula to output a cell reference

This formula will return the address of the same cell that this formula is
in.....

Vaya con Dios,
Chuck, CABGx3

"Creator" wrote:

> Ok, thanks again for indulging but I don't want any reference in the formula
> to the current cell address, whether by name or otherwise. I want to use this
> formula over many ranges. Is this possible? It's kinda like the cell asking
> itself "=who am I?"
> --
> Creator
>
>
> "CLR" wrote:
>
> > Yes, if you first give A1 a RangeName.........Insert > Name > Define.....type
> > in say, MyCell......
> >
> > then this formula, anywhere, will return \$A\$1.........
> >
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> >
> > "Creator" wrote:
> >
> > > Thanks for your response.
> > >
> > > Can it be done without A1 appearing in the formula?
> > > --
> > > Creator
> > >
> > >
> > > "CLR" wrote:
> > >
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > > "Creator" wrote:
> > > >
> > > > > Hi, is there a formula that would have as it's output the cell address of the
> > > > > same cell where the formula has been entered.
> > > > >
> > > > > e.g. In cell A10 I input a formula whose output is the cell reference A10.
> > > > >
> > > > > I'm hoping that this should be simple.
> > > > >
> > > > > --
> > > > > Creator

8. ## RE: Function/ formula to output a cell reference

Thanks "CLR" it works.
--
Creator

"CLR" wrote:

> This formula will return the address of the same cell that this formula is
> in.....
>
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Creator" wrote:
>
> > Ok, thanks again for indulging but I don't want any reference in the formula
> > to the current cell address, whether by name or otherwise. I want to use this
> > formula over many ranges. Is this possible? It's kinda like the cell asking
> > itself "=who am I?"
> > --
> > Creator
> >
> >
> > "CLR" wrote:
> >
> > > Yes, if you first give A1 a RangeName.........Insert > Name > Define.....type
> > > in say, MyCell......
> > >
> > > then this formula, anywhere, will return \$A\$1.........
> > >
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > >
> > >
> > > "Creator" wrote:
> > >
> > > > Thanks for your response.
> > > >
> > > > Can it be done without A1 appearing in the formula?
> > > > --
> > > > Creator
> > > >
> > > >
> > > > "CLR" wrote:
> > > >
> > > > > =CELL("address",A1)
> > > > >
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > >
> > > > > "Creator" wrote:
> > > > >
> > > > > > Hi, is there a formula that would have as it's output the cell address of the
> > > > > > same cell where the formula has been entered.
> > > > > >
> > > > > > e.g. In cell A10 I input a formula whose output is the cell reference A10.
> > > > > >
> > > > > > I'm hoping that this should be simple.
> > > > > >
> > > > > > --
> > > > > > Creator

9. ## Re: Function/ formula to output a cell reference

> This formula will return the address of the same cell that this
> formula is
> in.....
>

NO, NO, NO.

It will return the address of the Active Cell when a calculation
occurs. To illustrate, put =CELL("address") in Sheet1!A1. Then
switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do a
full recalc. Examine the contents of Sheet1!A1. It is not its own

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"CLR" <CLR@discussions.microsoft.com> wrote in message
> This formula will return the address of the same cell that this
> formula is
> in.....
>
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Creator" wrote:
>
>> Ok, thanks again for indulging but I don't want any reference
>> in the formula
>> to the current cell address, whether by name or otherwise. I
>> want to use this
>> formula over many ranges. Is this possible? It's kinda like
>> itself "=who am I?"
>> --
>> Creator
>>
>>
>> "CLR" wrote:
>>
>> > Yes, if you first give A1 a RangeName.........Insert > Name
>> > > Define.....type
>> > in say, MyCell......
>> >
>> > then this formula, anywhere, will return \$A\$1.........
>> >
>> >
>> > Vaya con Dios,
>> > Chuck, CABGx3
>> >
>> >
>> >
>> >
>> >
>> > "Creator" wrote:
>> >
>> > > Thanks for your response.
>> > >
>> > > Can it be done without A1 appearing in the formula?
>> > > --
>> > > Creator
>> > >
>> > >
>> > > "CLR" wrote:
>> > >
>> > > >
>> > > > Vaya con Dios,
>> > > > Chuck, CABGx3
>> > > >
>> > > >
>> > > > "Creator" wrote:
>> > > >
>> > > > > Hi, is there a formula that would have as it's output
>> > > > > the cell address of the
>> > > > > same cell where the formula has been entered.
>> > > > >
>> > > > > e.g. In cell A10 I input a formula whose output is the
>> > > > > cell reference A10.
>> > > > >
>> > > > > I'm hoping that this should be simple.
>> > > > >
>> > > > > --
>> > > > > Creator

10. ## Re: Function/ formula to output a cell reference

Creator wrote...
>Ok, thanks again for indulging but I don't want any reference in the formula
>to the current cell address, whether by name or otherwise. I want to use this
>formula over many ranges. Is this possible? It's kinda like the cell asking
>itself "=who am I?"

If you want the address of the cell in that cell's formula, then if you
call as 2nd argument. But this is *not* a problem since if cell B2
contained =CELL("Address",B2), B2 were copied and pasted into cell D7,
then the formula in cell D7 would be =CELL("Address",D7).

If your concern is manually entering such a term in formulas, then
switch to R1C1-style addressing when entering such formulas and enter
afterwards. Alternatively, if you enjoy unnecessary typing exercises,

11. ## Re: Function/ formula to output a cell reference

Hi Chip, is there a formula that you know of that will do what I have asked?
--
Creator

"Chip Pearson" wrote:

> > This formula will return the address of the same cell that this
> > formula is
> > in.....
> >

>
> NO, NO, NO.
>
> It will return the address of the Active Cell when a calculation
> occurs. To illustrate, put =CELL("address") in Sheet1!A1. Then
> switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do a
> full recalc. Examine the contents of Sheet1!A1. It is not its own
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "CLR" <CLR@discussions.microsoft.com> wrote in message
> > This formula will return the address of the same cell that this
> > formula is
> > in.....
> >
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Creator" wrote:
> >
> >> Ok, thanks again for indulging but I don't want any reference
> >> in the formula
> >> to the current cell address, whether by name or otherwise. I
> >> want to use this
> >> formula over many ranges. Is this possible? It's kinda like
> >> itself "=who am I?"
> >> --
> >> Creator
> >>
> >>
> >> "CLR" wrote:
> >>
> >> > Yes, if you first give A1 a RangeName.........Insert > Name
> >> > > Define.....type
> >> > in say, MyCell......
> >> >
> >> > then this formula, anywhere, will return \$A\$1.........
> >> >
> >> >
> >> > Vaya con Dios,
> >> > Chuck, CABGx3
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > "Creator" wrote:
> >> >
> >> > > Thanks for your response.
> >> > >
> >> > > Can it be done without A1 appearing in the formula?
> >> > > --
> >> > > Creator
> >> > >
> >> > >
> >> > > "CLR" wrote:
> >> > >
> >> > > > =CELL("address",A1)
> >> > > >
> >> > > > Vaya con Dios,
> >> > > > Chuck, CABGx3
> >> > > >
> >> > > >
> >> > > > "Creator" wrote:
> >> > > >
> >> > > > > Hi, is there a formula that would have as it's output
> >> > > > > the cell address of the
> >> > > > > same cell where the formula has been entered.
> >> > > > >
> >> > > > > e.g. In cell A10 I input a formula whose output is the
> >> > > > > cell reference A10.
> >> > > > >
> >> > > > > I'm hoping that this should be simple.
> >> > > > >
> >> > > > > --
> >> > > > > Creator

>
>
>

12. ## Re: Function/ formula to output a cell reference

Ok, thank you very much for the explanations. It clarified the situation.
--
Creator

"Harlan Grove" wrote:

> Creator wrote...
> >Ok, thanks again for indulging but I don't want any reference in the formula
> >to the current cell address, whether by name or otherwise. I want to use this
> >formula over many ranges. Is this possible? It's kinda like the cell asking
> >itself "=who am I?"

>
> If you want the address of the cell in that cell's formula, then if you
> use CELL("Address",..) you *MUST* put that cell's address in the CELL
> call as 2nd argument. But this is *not* a problem since if cell B2
> contained =CELL("Address",B2), B2 were copied and pasted into cell D7,
> then the formula in cell D7 would be =CELL("Address",D7).
>
> If your concern is manually entering such a term in formulas, then
> switch to R1C1-style addressing when entering such formulas and enter
> afterwards. Alternatively, if you enjoy unnecessary typing exercises,
>
>

13. ## Re: Function/ formula to output a cell reference

I still don't understand why you want a formula that simply
returns its own address -- it seems useless to me. However, you
can do it with

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Creator" <Creator@discussions.microsoft.com> wrote in message
news:B7382D54-73E6-4245-AF32-05EA0F2F4D8C@microsoft.com...
> Hi Chip, is there a formula that you know of that will do what
> --
> Creator
>
>
> "Chip Pearson" wrote:
>
>> > This formula will return the address of the same cell that
>> > this
>> > formula is
>> > in.....
>> >

>>
>> NO, NO, NO.
>>
>> It will return the address of the Active Cell when a
>> calculation
>> occurs. To illustrate, put =CELL("address") in Sheet1!A1.
>> Then
>> switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do
>> a
>> full recalc. Examine the contents of Sheet1!A1. It is not its
>> own
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "CLR" <CLR@discussions.microsoft.com> wrote in message
>> > This formula will return the address of the same cell that
>> > this
>> > formula is
>> > in.....
>> >
>> >
>> > Vaya con Dios,
>> > Chuck, CABGx3
>> >
>> >
>> >
>> > "Creator" wrote:
>> >
>> >> Ok, thanks again for indulging but I don't want any
>> >> reference
>> >> in the formula
>> >> to the current cell address, whether by name or otherwise.
>> >> I
>> >> want to use this
>> >> formula over many ranges. Is this possible? It's kinda like
>> >> itself "=who am I?"
>> >> --
>> >> Creator
>> >>
>> >>
>> >> "CLR" wrote:
>> >>
>> >> > Yes, if you first give A1 a RangeName.........Insert >
>> >> > Name
>> >> > > Define.....type
>> >> > in say, MyCell......
>> >> >
>> >> > then this formula, anywhere, will return \$A\$1.........
>> >> >
>> >> >
>> >> > Vaya con Dios,
>> >> > Chuck, CABGx3
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Creator" wrote:
>> >> >
>> >> > > Thanks for your response.
>> >> > >
>> >> > > Can it be done without A1 appearing in the formula?
>> >> > > --
>> >> > > Creator
>> >> > >
>> >> > >
>> >> > > "CLR" wrote:
>> >> > >
>> >> > > > =CELL("address",A1)
>> >> > > >
>> >> > > > Vaya con Dios,
>> >> > > > Chuck, CABGx3
>> >> > > >
>> >> > > >
>> >> > > > "Creator" wrote:
>> >> > > >
>> >> > > > > Hi, is there a formula that would have as it's
>> >> > > > > output
>> >> > > > > the cell address of the
>> >> > > > > same cell where the formula has been entered.
>> >> > > > >
>> >> > > > > e.g. In cell A10 I input a formula whose output is
>> >> > > > > the
>> >> > > > > cell reference A10.
>> >> > > > >
>> >> > > > > I'm hoping that this should be simple.
>> >> > > > >
>> >> > > > > --
>> >> > > > > Creator

>>
>>
>>

14. ## Re: Function/ formula to output a cell reference

Chip,

> switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do a full
> recalc

In my XL 97, CTRL+ALT+F9 does nothing that I can see but simply pressing F9
forces a recalculation.

Is it different for later versions?

--
Regards

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"Chip Pearson" <chip@cpearson.com> wrote in message
news:OonYGOzMGHA.1536@TK2MSFTNGP11.phx.gbl...
>> This formula will return the address of the same cell that this formula
>> is
>> in.....
>>

>
> NO, NO, NO.
>
> It will return the address of the Active Cell when a calculation occurs.
> To illustrate, put =CELL("address") in Sheet1!A1. Then switch to sheet3,
> select cell G10, and then CTRL+ALT+F9 to do a full recalc. Examine the
> contents of Sheet1!A1. It is not its own address; it is the address of
> Sheet3!G10.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "CLR" <CLR@discussions.microsoft.com> wrote in message
>> This formula will return the address of the same cell that this formula
>> is
>> in.....
>>
>>
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>>
>>
>> "Creator" wrote:
>>
>>> Ok, thanks again for indulging but I don't want any reference in the
>>> formula
>>> to the current cell address, whether by name or otherwise. I want to use
>>> this
>>> formula over many ranges. Is this possible? It's kinda like the cell
>>> itself "=who am I?"
>>> --
>>> Creator
>>>
>>>
>>> "CLR" wrote:
>>>
>>> > Yes, if you first give A1 a RangeName.........Insert > Name >
>>> > Define.....type
>>> > in say, MyCell......
>>> >
>>> > then this formula, anywhere, will return \$A\$1.........
>>> >
>>> >
>>> > Vaya con Dios,
>>> > Chuck, CABGx3
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > "Creator" wrote:
>>> >
>>> > > Thanks for your response.
>>> > >
>>> > > Can it be done without A1 appearing in the formula?
>>> > > --
>>> > > Creator
>>> > >
>>> > >
>>> > > "CLR" wrote:
>>> > >
>>> > > >
>>> > > > Vaya con Dios,
>>> > > > Chuck, CABGx3
>>> > > >
>>> > > >
>>> > > > "Creator" wrote:
>>> > > >
>>> > > > > Hi, is there a formula that would have as it's output the cell
>>> > > > > address of the
>>> > > > > same cell where the formula has been entered.
>>> > > > >
>>> > > > > e.g. In cell A10 I input a formula whose output is the cell
>>> > > > > reference A10.
>>> > > > >
>>> > > > > I'm hoping that this should be simple.
>>> > > > >
>>> > > > > --
>>> > > > > Creator

>
>

15. ## Re: Function/ formula to output a cell reference

Thanks Chip, it works. I need it to help me build approximately 8,000
formulae in a spreadsheet I'm working on. I'm not sure that it will work but
I believe the solution to my problem lies with this function.

Thanks again for taking the time.

best regards
--
Creator

"Chip Pearson" wrote:

> I still don't understand why you want a formula that simply
> returns its own address -- it seems useless to me. However, you
> can do it with
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Creator" <Creator@discussions.microsoft.com> wrote in message
> news:B7382D54-73E6-4245-AF32-05EA0F2F4D8C@microsoft.com...
> > Hi Chip, is there a formula that you know of that will do what
> > --
> > Creator
> >
> >
> > "Chip Pearson" wrote:
> >
> >> > This formula will return the address of the same cell that
> >> > this
> >> > formula is
> >> > in.....
> >> >
> >>
> >> NO, NO, NO.
> >>
> >> It will return the address of the Active Cell when a
> >> calculation
> >> occurs. To illustrate, put =CELL("address") in Sheet1!A1.
> >> Then
> >> switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do
> >> a
> >> full recalc. Examine the contents of Sheet1!A1. It is not its
> >> own
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >>
> >> "CLR" <CLR@discussions.microsoft.com> wrote in message
> >> > This formula will return the address of the same cell that
> >> > this
> >> > formula is
> >> > in.....
> >> >
> >> >
> >> > Vaya con Dios,
> >> > Chuck, CABGx3
> >> >
> >> >
> >> >
> >> > "Creator" wrote:
> >> >
> >> >> Ok, thanks again for indulging but I don't want any
> >> >> reference
> >> >> in the formula
> >> >> to the current cell address, whether by name or otherwise.
> >> >> I
> >> >> want to use this
> >> >> formula over many ranges. Is this possible? It's kinda like
> >> >> the cell asking
> >> >> itself "=who am I?"
> >> >> --
> >> >> Creator
> >> >>
> >> >>
> >> >> "CLR" wrote:
> >> >>
> >> >> > Yes, if you first give A1 a RangeName.........Insert >
> >> >> > Name
> >> >> > > Define.....type
> >> >> > in say, MyCell......
> >> >> >
> >> >> > then this formula, anywhere, will return \$A\$1.........
> >> >> >
> >> >> >
> >> >> > Vaya con Dios,
> >> >> > Chuck, CABGx3
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Creator" wrote:
> >> >> >
> >> >> > > Thanks for your response.
> >> >> > >
> >> >> > > Can it be done without A1 appearing in the formula?
> >> >> > > --
> >> >> > > Creator
> >> >> > >
> >> >> > >
> >> >> > > "CLR" wrote:
> >> >> > >
> >> >> > > > =CELL("address",A1)
> >> >> > > >
> >> >> > > > Vaya con Dios,
> >> >> > > > Chuck, CABGx3
> >> >> > > >
> >> >> > > >
> >> >> > > > "Creator" wrote:
> >> >> > > >
> >> >> > > > > Hi, is there a formula that would have as it's
> >> >> > > > > output
> >> >> > > > > the cell address of the
> >> >> > > > > same cell where the formula has been entered.
> >> >> > > > >
> >> >> > > > > e.g. In cell A10 I input a formula whose output is
> >> >> > > > > the
> >> >> > > > > cell reference A10.
> >> >> > > > >
> >> >> > > > > I'm hoping that this should be simple.
> >> >> > > > >
> >> >> > > > > --
> >> >> > > > > Creator
> >>
> >>
> >>

>
>
>

16. ## Re: Function/ formula to output a cell reference

Chip, why does this formula not work?

--
Creator

"Chip Pearson" wrote:

> I still don't understand why you want a formula that simply
> returns its own address -- it seems useless to me. However, you
> can do it with
>
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Creator" <Creator@discussions.microsoft.com> wrote in message
> news:B7382D54-73E6-4245-AF32-05EA0F2F4D8C@microsoft.com...
> > Hi Chip, is there a formula that you know of that will do what
> > --
> > Creator
> >
> >
> > "Chip Pearson" wrote:
> >
> >> > This formula will return the address of the same cell that
> >> > this
> >> > formula is
> >> > in.....
> >> >
> >>
> >> NO, NO, NO.
> >>
> >> It will return the address of the Active Cell when a
> >> calculation
> >> occurs. To illustrate, put =CELL("address") in Sheet1!A1.
> >> Then
> >> switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do
> >> a
> >> full recalc. Examine the contents of Sheet1!A1. It is not its
> >> own
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >>
> >> "CLR" <CLR@discussions.microsoft.com> wrote in message
> >> > This formula will return the address of the same cell that
> >> > this
> >> > formula is
> >> > in.....
> >> >
> >> >
> >> > Vaya con Dios,
> >> > Chuck, CABGx3
> >> >
> >> >
> >> >
> >> > "Creator" wrote:
> >> >
> >> >> Ok, thanks again for indulging but I don't want any
> >> >> reference
> >> >> in the formula
> >> >> to the current cell address, whether by name or otherwise.
> >> >> I
> >> >> want to use this
> >> >> formula over many ranges. Is this possible? It's kinda like
> >> >> the cell asking
> >> >> itself "=who am I?"
> >> >> --
> >> >> Creator
> >> >>
> >> >>
> >> >> "CLR" wrote:
> >> >>
> >> >> > Yes, if you first give A1 a RangeName.........Insert >
> >> >> > Name
> >> >> > > Define.....type
> >> >> > in say, MyCell......
> >> >> >
> >> >> > then this formula, anywhere, will return \$A\$1.........
> >> >> >
> >> >> >
> >> >> > Vaya con Dios,
> >> >> > Chuck, CABGx3
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Creator" wrote:
> >> >> >
> >> >> > > Thanks for your response.
> >> >> > >
> >> >> > > Can it be done without A1 appearing in the formula?
> >> >> > > --
> >> >> > > Creator
> >> >> > >
> >> >> > >
> >> >> > > "CLR" wrote:
> >> >> > >
> >> >> > > > =CELL("address",A1)
> >> >> > > >
> >> >> > > > Vaya con Dios,
> >> >> > > > Chuck, CABGx3
> >> >> > > >
> >> >> > > >
> >> >> > > > "Creator" wrote:
> >> >> > > >
> >> >> > > > > Hi, is there a formula that would have as it's
> >> >> > > > > output
> >> >> > > > > the cell address of the
> >> >> > > > > same cell where the formula has been entered.
> >> >> > > > >
> >> >> > > > > e.g. In cell A10 I input a formula whose output is
> >> >> > > > > the
> >> >> > > > > cell reference A10.
> >> >> > > > >
> >> >> > > > > I'm hoping that this should be simple.
> >> >> > > > >
> >> >> > > > > --
> >> >> > > > > Creator
> >>
> >>
> >>

>
>
>

17. ## Re: Function/ formula to output a cell reference

I am not Chip but ADDRESS returns a text string so you need to use indirect
as well

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

"Creator" <Creator@discussions.microsoft.com> wrote in message
news:38C4C6C3-12B5-48F9-8B97-3758368BE714@microsoft.com...
> Chip, why does this formula not work?
>
>
> --
> Creator
>
>
> "Chip Pearson" wrote:
>
>> I still don't understand why you want a formula that simply
>> returns its own address -- it seems useless to me. However, you
>> can do it with
>>
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Creator" <Creator@discussions.microsoft.com> wrote in message
>> news:B7382D54-73E6-4245-AF32-05EA0F2F4D8C@microsoft.com...
>> > Hi Chip, is there a formula that you know of that will do what
>> > --
>> > Creator
>> >
>> >
>> > "Chip Pearson" wrote:
>> >
>> >> > This formula will return the address of the same cell that
>> >> > this
>> >> > formula is
>> >> > in.....
>> >> >
>> >>
>> >> NO, NO, NO.
>> >>
>> >> It will return the address of the Active Cell when a
>> >> calculation
>> >> occurs. To illustrate, put =CELL("address") in Sheet1!A1.
>> >> Then
>> >> switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do
>> >> a
>> >> full recalc. Examine the contents of Sheet1!A1. It is not its
>> >> own
>> >>
>> >>
>> >> --
>> >> Cordially,
>> >> Chip Pearson
>> >> Microsoft MVP - Excel
>> >> Pearson Software Consulting, LLC
>> >> www.cpearson.com
>> >>
>> >>
>> >>
>> >>
>> >> "CLR" <CLR@discussions.microsoft.com> wrote in message
>> >> > This formula will return the address of the same cell that
>> >> > this
>> >> > formula is
>> >> > in.....
>> >> >
>> >> >
>> >> > Vaya con Dios,
>> >> > Chuck, CABGx3
>> >> >
>> >> >
>> >> >
>> >> > "Creator" wrote:
>> >> >
>> >> >> Ok, thanks again for indulging but I don't want any
>> >> >> reference
>> >> >> in the formula
>> >> >> to the current cell address, whether by name or otherwise.
>> >> >> I
>> >> >> want to use this
>> >> >> formula over many ranges. Is this possible? It's kinda like
>> >> >> the cell asking
>> >> >> itself "=who am I?"
>> >> >> --
>> >> >> Creator
>> >> >>
>> >> >>
>> >> >> "CLR" wrote:
>> >> >>
>> >> >> > Yes, if you first give A1 a RangeName.........Insert >
>> >> >> > Name
>> >> >> > > Define.....type
>> >> >> > in say, MyCell......
>> >> >> >
>> >> >> > then this formula, anywhere, will return \$A\$1.........
>> >> >> >
>> >> >> >
>> >> >> > Vaya con Dios,
>> >> >> > Chuck, CABGx3
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "Creator" wrote:
>> >> >> >
>> >> >> > > Thanks for your response.
>> >> >> > >
>> >> >> > > Can it be done without A1 appearing in the formula?
>> >> >> > > --
>> >> >> > > Creator
>> >> >> > >
>> >> >> > >
>> >> >> > > "CLR" wrote:
>> >> >> > >
>> >> >> > > > =CELL("address",A1)
>> >> >> > > >
>> >> >> > > > Vaya con Dios,
>> >> >> > > > Chuck, CABGx3
>> >> >> > > >
>> >> >> > > >
>> >> >> > > > "Creator" wrote:
>> >> >> > > >
>> >> >> > > > > Hi, is there a formula that would have as it's
>> >> >> > > > > output
>> >> >> > > > > the cell address of the
>> >> >> > > > > same cell where the formula has been entered.
>> >> >> > > > >
>> >> >> > > > > e.g. In cell A10 I input a formula whose output is
>> >> >> > > > > the
>> >> >> > > > > cell reference A10.
>> >> >> > > > >
>> >> >> > > > > I'm hoping that this should be simple.
>> >> >> > > > >
>> >> >> > > > > --
>> >> >> > > > > Creator
>> >>
>> >>
>> >>

>>
>>
>>

18. ## Re: Function/ formula to output a cell reference

Here's from Excel 2003 help

Press F9 Calculates formulas that have changed since the last calculation,
and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last
calculation,
and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks,
regardless
of whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed
since
last time or not.

I don't have 97 but I have 95 and Ctrl + Alt + F9 does the same as in 2003
so I am sure it's the same
in 97. I believe number 4 is the one that has been added

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:Og4IyU0MGHA.1536@TK2MSFTNGP11.phx.gbl...
> Chip,
>
>> switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do a full
>> recalc

>
> In my XL 97, CTRL+ALT+F9 does nothing that I can see but simply pressing
> F9 forces a recalculation.
>
> Is it different for later versions?
>
> --
> Regards
>
> Sandy
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:OonYGOzMGHA.1536@TK2MSFTNGP11.phx.gbl...
>>> This formula will return the address of the same cell that this formula
>>> is
>>> in.....
>>>

>>
>> NO, NO, NO.
>>
>> It will return the address of the Active Cell when a calculation occurs.
>> To illustrate, put =CELL("address") in Sheet1!A1. Then switch to sheet3,
>> select cell G10, and then CTRL+ALT+F9 to do a full recalc. Examine the
>> contents of Sheet1!A1. It is not its own address; it is the address of
>> Sheet3!G10.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "CLR" <CLR@discussions.microsoft.com> wrote in message
>>> This formula will return the address of the same cell that this formula
>>> is
>>> in.....
>>>
>>>
>>> Vaya con Dios,
>>> Chuck, CABGx3
>>>
>>>
>>>
>>> "Creator" wrote:
>>>
>>>> Ok, thanks again for indulging but I don't want any reference in the
>>>> formula
>>>> to the current cell address, whether by name or otherwise. I want to
>>>> use this
>>>> formula over many ranges. Is this possible? It's kinda like the cell
>>>> itself "=who am I?"
>>>> --
>>>> Creator
>>>>
>>>>
>>>> "CLR" wrote:
>>>>
>>>> > Yes, if you first give A1 a RangeName.........Insert > Name >
>>>> > Define.....type
>>>> > in say, MyCell......
>>>> >
>>>> > then this formula, anywhere, will return \$A\$1.........
>>>> >
>>>> >
>>>> > Vaya con Dios,
>>>> > Chuck, CABGx3
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> > "Creator" wrote:
>>>> >
>>>> > > Thanks for your response.
>>>> > >
>>>> > > Can it be done without A1 appearing in the formula?
>>>> > > --
>>>> > > Creator
>>>> > >
>>>> > >
>>>> > > "CLR" wrote:
>>>> > >
>>>> > > >
>>>> > > > Vaya con Dios,
>>>> > > > Chuck, CABGx3
>>>> > > >
>>>> > > >
>>>> > > > "Creator" wrote:
>>>> > > >
>>>> > > > > Hi, is there a formula that would have as it's output the cell
>>>> > > > > address of the
>>>> > > > > same cell where the formula has been entered.
>>>> > > > >
>>>> > > > > e.g. In cell A10 I input a formula whose output is the cell
>>>> > > > > reference A10.
>>>> > > > >
>>>> > > > > I'm hoping that this should be simple.
>>>> > > > >
>>>> > > > > --
>>>> > > > > Creator

>>
>>

>
>

19. ## Re: Function/ formula to output a cell reference

Thanks a lot Peo, it works.
--
Creator

"Peo Sjoblom" wrote:

> I am not Chip but ADDRESS returns a text string so you need to use indirect
> as well
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> Portland, Oregon
>
>
>
>
> "Creator" <Creator@discussions.microsoft.com> wrote in message
> news:38C4C6C3-12B5-48F9-8B97-3758368BE714@microsoft.com...
> > Chip, why does this formula not work?
> >
> >
> > --
> > Creator
> >
> >
> > "Chip Pearson" wrote:
> >
> >> I still don't understand why you want a formula that simply
> >> returns its own address -- it seems useless to me. However, you
> >> can do it with
> >>
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "Creator" <Creator@discussions.microsoft.com> wrote in message
> >> news:B7382D54-73E6-4245-AF32-05EA0F2F4D8C@microsoft.com...
> >> > Hi Chip, is there a formula that you know of that will do what
> >> > I have asked?
> >> > --
> >> > Creator
> >> >
> >> >
> >> > "Chip Pearson" wrote:
> >> >
> >> >> > This formula will return the address of the same cell that
> >> >> > this
> >> >> > formula is
> >> >> > in.....
> >> >> >
> >> >>
> >> >> NO, NO, NO.
> >> >>
> >> >> It will return the address of the Active Cell when a
> >> >> calculation
> >> >> occurs. To illustrate, put =CELL("address") in Sheet1!A1.
> >> >> Then
> >> >> switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do
> >> >> a
> >> >> full recalc. Examine the contents of Sheet1!A1. It is not its
> >> >> own
> >> >>
> >> >>
> >> >> --
> >> >> Cordially,
> >> >> Chip Pearson
> >> >> Microsoft MVP - Excel
> >> >> Pearson Software Consulting, LLC
> >> >> www.cpearson.com
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "CLR" <CLR@discussions.microsoft.com> wrote in message
> >> >> > This formula will return the address of the same cell that
> >> >> > this
> >> >> > formula is
> >> >> > in.....
> >> >> >
> >> >> >
> >> >> > Vaya con Dios,
> >> >> > Chuck, CABGx3
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Creator" wrote:
> >> >> >
> >> >> >> Ok, thanks again for indulging but I don't want any
> >> >> >> reference
> >> >> >> in the formula
> >> >> >> to the current cell address, whether by name or otherwise.
> >> >> >> I
> >> >> >> want to use this
> >> >> >> formula over many ranges. Is this possible? It's kinda like
> >> >> >> the cell asking
> >> >> >> itself "=who am I?"
> >> >> >> --
> >> >> >> Creator
> >> >> >>
> >> >> >>
> >> >> >> "CLR" wrote:
> >> >> >>
> >> >> >> > Yes, if you first give A1 a RangeName.........Insert >
> >> >> >> > Name
> >> >> >> > > Define.....type
> >> >> >> > in say, MyCell......
> >> >> >> >
> >> >> >> > then this formula, anywhere, will return \$A\$1.........
> >> >> >> >
> >> >> >> > =CELL("address",MyCell)
> >> >> >> >
> >> >> >> > Vaya con Dios,
> >> >> >> > Chuck, CABGx3
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> > "Creator" wrote:
> >> >> >> >
> >> >> >> > > Thanks for your response.
> >> >> >> > >
> >> >> >> > > Can it be done without A1 appearing in the formula?
> >> >> >> > > --
> >> >> >> > > Creator
> >> >> >> > >
> >> >> >> > >
> >> >> >> > > "CLR" wrote:
> >> >> >> > >
> >> >> >> > > > =CELL("address",A1)
> >> >> >> > > >
> >> >> >> > > > Vaya con Dios,
> >> >> >> > > > Chuck, CABGx3
> >> >> >> > > >
> >> >> >> > > >
> >> >> >> > > > "Creator" wrote:
> >> >> >> > > >
> >> >> >> > > > > Hi, is there a formula that would have as it's
> >> >> >> > > > > output
> >> >> >> > > > > the cell address of the
> >> >> >> > > > > same cell where the formula has been entered.
> >> >> >> > > > >
> >> >> >> > > > > e.g. In cell A10 I input a formula whose output is
> >> >> >> > > > > the
> >> >> >> > > > > cell reference A10.
> >> >> >> > > > >
> >> >> >> > > > > I'm hoping that this should be simple.
> >> >> >> > > > >
> >> >> >> > > > > --
> >> >> >> > > > > Creator
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>

20. ## Re: Function/ formula to output a cell reference

Thank you Peo,

F9 works, SHIFT + F9 works but CTRL + ALT + F9 still doesn't do anything. I
am using XL 97 on Windows XP Home on a Samsung laptop. Perhaps the fact
that it is a laptop that is making the difference because if it works in 95
I would definitely expect it to work in 97. Another thought that just
occurred to me was perhaps I need to reinstall XL.

--
Regards

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:u4pM\$q%23MGHA.3284@TK2MSFTNGP14.phx.gbl...
> Here's from Excel 2003 help
>
> Press F9 Calculates formulas that have changed since the last calculation,
> and
> formulas dependent on them, in all open workbooks. If a workbook is set
> for
> automatic calculation, you do not need to press F9 for calculation.
>
> Press SHIFT+F9 Calculates formulas that have changed since the last
> calculation,
> and formulas dependent on them, in the active worksheet.
>
> Press CTRL+ALT+F9 Calculates all formulas in all open workbooks,
> regardless
> of whether they have changed since last time or not.
>
> Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates
> all
> formulas in all open workbooks, regardless of whether they have changed
> since
> last time or not.
>
> I don't have 97 but I have 95 and Ctrl + Alt + F9 does the same as in 2003
> so I am sure it's the same
> in 97. I believe number 4 is the one that has been added
>
> --
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> Portland, Oregon
>
>
>
>
> "Sandy Mann" <sandymann2@mailinator.com> wrote in message
> news:Og4IyU0MGHA.1536@TK2MSFTNGP11.phx.gbl...
>> Chip,
>>
>>> switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do a full
>>> recalc

>>
>> In my XL 97, CTRL+ALT+F9 does nothing that I can see but simply pressing
>> F9 forces a recalculation.
>>
>> Is it different for later versions?
>>
>> --
>> Regards
>>
>> Sandy
>> sandymann2@mailinator.com
>> Replace@mailinator.com with @tiscali.co.uk
>>
>>
>> "Chip Pearson" <chip@cpearson.com> wrote in message
>> news:OonYGOzMGHA.1536@TK2MSFTNGP11.phx.gbl...
>>>> This formula will return the address of the same cell that this formula
>>>> is
>>>> in.....
>>>>
>>>
>>> NO, NO, NO.
>>>
>>> It will return the address of the Active Cell when a calculation occurs.
>>> To illustrate, put =CELL("address") in Sheet1!A1. Then switch to
>>> sheet3, select cell G10, and then CTRL+ALT+F9 to do a full recalc.
>>> Examine the contents of Sheet1!A1. It is not its own address; it is the
>>>
>>>
>>> --
>>> Cordially,
>>> Chip Pearson
>>> Microsoft MVP - Excel
>>> Pearson Software Consulting, LLC
>>> www.cpearson.com
>>>
>>>
>>>
>>>
>>> "CLR" <CLR@discussions.microsoft.com> wrote in message
>>>> This formula will return the address of the same cell that this formula
>>>> is
>>>> in.....
>>>>
>>>>
>>>> Vaya con Dios,
>>>> Chuck, CABGx3
>>>>
>>>>
>>>>
>>>> "Creator" wrote:
>>>>
>>>>> Ok, thanks again for indulging but I don't want any reference in the
>>>>> formula
>>>>> to the current cell address, whether by name or otherwise. I want to
>>>>> use this
>>>>> formula over many ranges. Is this possible? It's kinda like the cell
>>>>> itself "=who am I?"
>>>>> --
>>>>> Creator
>>>>>
>>>>>
>>>>> "CLR" wrote:
>>>>>
>>>>> > Yes, if you first give A1 a RangeName.........Insert > Name >
>>>>> > Define.....type
>>>>> > in say, MyCell......
>>>>> >
>>>>> > then this formula, anywhere, will return \$A\$1.........
>>>>> >
>>>>> >
>>>>> > Vaya con Dios,
>>>>> > Chuck, CABGx3
>>>>> >
>>>>> >
>>>>> >
>>>>> >
>>>>> >
>>>>> > "Creator" wrote:
>>>>> >
>>>>> > > Thanks for your response.
>>>>> > >
>>>>> > > Can it be done without A1 appearing in the formula?
>>>>> > > --
>>>>> > > Creator
>>>>> > >
>>>>> > >
>>>>> > > "CLR" wrote:
>>>>> > >
>>>>> > > >
>>>>> > > > Vaya con Dios,
>>>>> > > > Chuck, CABGx3
>>>>> > > >
>>>>> > > >
>>>>> > > > "Creator" wrote:
>>>>> > > >
>>>>> > > > > Hi, is there a formula that would have as it's output the cell
>>>>> > > > > address of the
>>>>> > > > > same cell where the formula has been entered.
>>>>> > > > >
>>>>> > > > > e.g. In cell A10 I input a formula whose output is the cell
>>>>> > > > > reference A10.
>>>>> > > > >
>>>>> > > > > I'm hoping that this should be simple.
>>>>> > > > >
>>>>> > > > > --
>>>>> > > > > Creator
>>>
>>>

>>
>>

>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1