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

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

=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

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

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

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

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

=CELL("address")

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

Thanks "CLR" it works.
--
Creator


"CLR" wrote:

> This formula will return the address of the same cell that this formula is
> in.....
>
> =CELL("address")
>
> 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

> This formula will return the address of the same cell that this
> formula is
> in.....
>
> =CELL("address")

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" <[email protected]> wrote in message
news:[email protected]...
> This formula will return the address of the same cell that this
> formula is
> in.....
>
> =CELL("address")
>
> 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

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
=CELL("Address",RC), then switch back to A1-style addressing
afterwards. Alternatively, if you enjoy unnecessary typing exercises,
you could use =ADDRESS(ROW(),COLUMN()).

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.....
> >
> > =CELL("address")
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > This formula will return the address of the same cell that this
> > formula is
> > in.....
> >
> > =CELL("address")
> >
> > 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
>
>
>

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
> =CELL("Address",RC), then switch back to A1-style addressing
> afterwards. Alternatively, if you enjoy unnecessary typing exercises,
> you could use =ADDRESS(ROW(),COLUMN()).
>
>

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

=ADDRESS(ROW(),COLUMN())


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


"Creator" <[email protected]> wrote in message
news:[email protected]...
> 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.....
>> >
>> > =CELL("address")
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > This formula will return the address of the same cell that
>> > this
>> > formula is
>> > in.....
>> >
>> > =CELL("address")
>> >
>> > 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
>>
>>
>>

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
[email protected]
[email protected] with @tiscali.co.uk


"Chip Pearson" <[email protected]> wrote in message
news:[email protected]...
>> This formula will return the address of the same cell that this formula
>> is
>> in.....
>>
>> =CELL("address")
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> This formula will return the address of the same cell that this formula
>> is
>> in.....
>>
>> =CELL("address")
>>
>> 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
>
>

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
>
> =ADDRESS(ROW(),COLUMN())
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Creator" <[email protected]> wrote in message
> news:[email protected]...
> > 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.....
> >> >
> >> > =CELL("address")
> >>
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > This formula will return the address of the same cell that
> >> > this
> >> > formula is
> >> > in.....
> >> >
> >> > =CELL("address")
> >> >
> >> > 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
> >>
> >>
> >>
>
>
>

Chip, why does this formula not work?

=offset(ADDRESS(ROW(),COLUMN()),1,1,1,1)

--
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
>
> =ADDRESS(ROW(),COLUMN())
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Creator" <[email protected]> wrote in message
> news:[email protected]...
> > 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.....
> >> >
> >> > =CELL("address")
> >>
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > This formula will return the address of the same cell that
> >> > this
> >> > formula is
> >> > in.....
> >> >
> >> > =CELL("address")
> >> >
> >> > 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
> >>
> >>
> >>
>
>
>

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

=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),1,1,1,1)

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"Creator" <[email protected]> wrote in message
news:[email protected]...
> Chip, why does this formula not work?
>
> =offset(ADDRESS(ROW(),COLUMN()),1,1,1,1)
>
> --
> 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
>>
>> =ADDRESS(ROW(),COLUMN())
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Creator" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.....
>> >> >
>> >> > =CELL("address")
>> >>
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > This formula will return the address of the same cell that
>> >> > this
>> >> > formula is
>> >> > in.....
>> >> >
>> >> > =CELL("address")
>> >> >
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Chip Pearson" <[email protected]> wrote in message
> news:[email protected]...
>>> This formula will return the address of the same cell that this formula
>>> is
>>> in.....
>>>
>>> =CELL("address")
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> This formula will return the address of the same cell that this formula
>>> is
>>> in.....
>>>
>>> =CELL("address")
>>>
>>> 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
>>
>>
>
>

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
>
> =OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),1,1,1,1)
>
> --
> Regards,
>
> Peo Sjoblom
>
> Northwest Excel Solutions
>
> Portland, Oregon
>
>
>
>
> "Creator" <[email protected]> wrote in message
> news:[email protected]...
> > Chip, why does this formula not work?
> >
> > =offset(ADDRESS(ROW(),COLUMN()),1,1,1,1)
> >
> > --
> > 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
> >>
> >> =ADDRESS(ROW(),COLUMN())
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "Creator" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.....
> >> >> >
> >> >> > =CELL("address")
> >> >>
> >> >> 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > This formula will return the address of the same cell that
> >> >> > this
> >> >> > formula is
> >> >> > in.....
> >> >> >
> >> >> > =CELL("address")
> >> >> >
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

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
[email protected]
[email protected] with @tiscali.co.uk


"Peo Sjoblom" <[email protected]> wrote in message
news:u4pM$q%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "Chip Pearson" <[email protected]> wrote in message
>> news:[email protected]...
>>>> This formula will return the address of the same cell that this formula
>>>> is
>>>> in.....
>>>>
>>>> =CELL("address")
>>>
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> This formula will return the address of the same cell that this formula
>>>> is
>>>> in.....
>>>>
>>>> =CELL("address")
>>>>
>>>> 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
>>>
>>>
>>
>>
>