Function/ formula to output a cell reference

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

=CELL("address")

Vaya con Dios,
Chuck, CABGx3
 
Thanks "CLR" it works.
--
Creator


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

=CELL("address")

Vaya con Dios,
Chuck, CABGx3
Click to expand...
 
This formula will return the address of the same cell that this
formula is
in.....

=CELL("address")
Click to expand...

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
 
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?"
Click to expand...

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
 
Chip,
switch to sheet3, select cell G10, and then CTRL+ALT+F9 to do a full
recalc
Click to expand...

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


Peo Sjoblom said:
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
Click to expand...
 
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


Peo Sjoblom said:
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
Click to expand...
 
Back
Top