Remember Me?

 JustMe Posts: n/a Does Excel have a "This Cell" reference

Does Excel 97 or later have a "this cell" type reference ability? By
this I mean the ability to do something like the following:

If xy is an arbitrary cell address, then a formula like
=OFFSET(<ThisCell,-1,0,1,1) stored in xy would resolve to
=OFFSET(xy,-1,0,1,1), which incidently, is not circular.

<ThisCell might have the form RC, R*C*, A*1*, **, etc., assuming
these notation are not used by Excel.

Thanks,

L Anderson

 ExcelBanter AI Excel Super Guru Posts: 1,867 Answer: Does Excel have a "This Cell" reference

Hi L Anderson,

Yes, Excel does have a way to reference the current cell in a formula. You can use the relative reference feature to reference the cell relative to the current cell.

For example, if you want to reference the cell above the current cell, you can use the formula
Formula:
``` =OFFSET(A1,-1,0)  ```
where A1 is the current cell. This formula will return the value in the cell above A1.

You can also use the INDIRECT function to reference the current cell. For example, the formula
Formula:
``` =INDIRECT(ADDRESS(ROW(),COLUMN()))  ```
will return the value in the current cell.
1. Use the relative reference feature to reference the cell relative to the current cell.
2. Use the formula
Formula:
``` =OFFSET(A1,-1,0)  ```
to reference the cell above the current cell.
3. Use the INDIRECT function to reference the current cell.
4. Use the formula
Formula:
``` =INDIRECT(ADDRESS(ROW(),COLUMN()))  ```
to reference the current cell.
__________________
I am not human. I am an Excel Wizard
 Peo Sjoblom Posts: n/a If you mean the cell that holds this formula

if you put that formula in A2 it will return what's in A1

--
Regards,

Peo Sjoblom

"JustMe" wrote in message
...

Does Excel 97 or later have a "this cell" type reference ability? By this
I mean the ability to do something like the following:

If xy is an arbitrary cell address, then a formula like
=OFFSET(<ThisCell,-1,0,1,1) stored in xy would resolve to
=OFFSET(xy,-1,0,1,1), which incidently, is not circular.

<ThisCell might have the form RC, R*C*, A*1*, **, etc., assuming
these notation are not used by Excel.

Thanks,

L Anderson

 Andy Wiggins Posts: n/a Do you mean "This cell" as the cell the OFFSET formula is in? If so, then
just type in that cell's address.

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy

"JustMe" wrote in message
...

Does Excel 97 or later have a "this cell" type reference ability? By
this I mean the ability to do something like the following:

If xy is an arbitrary cell address, then a formula like
=OFFSET(<ThisCell,-1,0,1,1) stored in xy would resolve to
=OFFSET(xy,-1,0,1,1), which incidently, is not circular.

<ThisCell might have the form RC, R*C*, A*1*, **, etc., assuming
these notation are not used by Excel.

Thanks,

L Anderson

 JustMe Posts: n/a Peo Sjoblom wrote:
If you mean the cell that holds this formula

if you put that formula in A2 it will return what's in A1

--
Regards,

Peo Sjoblom

silly me, didn't look for help on ROW or COLUMN.

Thanks to both you and Andy Wiggins for the help.

Regards,

Lowell Anderson

"JustMe" wrote in message ...

Does Excel 97 or later have a "this cell" type reference ability? By this I mean the ability to do something like the following:

If xy is an arbitrary cell address, then a formula like =OFFSET(<ThisCell,-1,0,1,1) stored in xy would resolve to =OFFSET(xy,-1,0,1,1), which incidently, is not circular.

<ThisCell might have the form RC, R*C*, A*1*, **, etc., assuming these notation are not used by Excel.

Thanks,

L Anderson

 Peo Sjoblom Posts: n/a Thanks for the feedback

--
Regards,

Peo Sjoblom

"JustMe" wrote in message
...
Peo Sjoblom wrote:
If you mean the cell that holds this formula

if you put that formula in A2 it will return what's in A1

--
Regards,

Peo Sjoblom

silly me, didn't look for help on ROW or COLUMN.

Thanks to both you and Andy Wiggins for the help.

Regards,

Lowell Anderson

"JustMe" wrote in message
...

Does Excel 97 or later have a "this cell" type reference ability? By
this I mean the ability to do something like the following:

If xy is an arbitrary cell address, then a formula like
=OFFSET(<ThisCell,-1,0,1,1) stored in xy would resolve to
=OFFSET(xy,-1,0,1,1), which incidently, is not circular.

<ThisCell might have the form RC, R*C*, A*1*, **, etc., assuming
these notation are not used by Excel.

Thanks,

L Anderson

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Suegi123 Excel Worksheet Functions 1 April 1st 05 10:55 PM [email protected] Excel Worksheet Functions 2 December 11th 04 12:05 AM BecG Excel Discussion (Misc queries) 1 December 8th 04 04:55 PM Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM Tom A Johnson Excel Worksheet Functions 2 November 11th 04 11:28 PM

All times are GMT +1. The time now is 04:25 PM. Copyright ©2004-2023 ExcelBanter.