Function/ formula to output a cell reference

[Guest]

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.

 

[Guest]

so... you want cell "A10" to read "A10"? I don't think I understand your post.

 

[Guest]

=CELL("address",A1)

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thanks for your response.

Can it be done without A1 appearing in the formula?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

=CELL("address")

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thanks "CLR" it works.
--
Creator

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

=CELL("address")

Vaya con Dios,
Chuck, CABGx3

 

[Chip Pearson]

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

 

[Harlan Grove]

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()).

 

[Guest]

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

 

[Guest]

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

 

[Chip Pearson]

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

 

[Sandy Mann]

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

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

 

[Guest]

Chip, why does this formula not work?

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

 

[Peo Sjoblom]

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

 

[Peo Sjoblom]

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

 

[Guest]

Thanks a lot Peo, it works.
--
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

 

[Sandy Mann]

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

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