Does Excel have a "This Cell" reference
 

I've googled n googled n searched but can not find the answer so I
thought I'd ask.

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 R[0]C[0], R*C*, A*1*, **, etc., assuming
these notation are not used by Excel.

Thanks,

L Anderson

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.

If you mean the cell that holds this formula


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

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

--
Regards,

Peo Sjoblom


"JustMe" wrote in message
...
I've googled n googled n searched but can not find the answer so I thought
I'd ask.

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 R[0]C[0], R*C*, A*1*, **, etc., assuming
these notation are not used by Excel.

Thanks,

L Anderson

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
...
I've googled n googled n searched but can not find the answer so I
thought I'd ask.

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 R[0]C[0], R*C*, A*1*, **, etc., assuming
these notation are not used by Excel.

Thanks,

L Anderson

Peo Sjoblom wrote:
If you mean the cell that holds this formula


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

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

--
Regards,

Peo Sjoblom


The answer I'm looking for is "ADDRESS(ROW(),COLUMN())". I had tried
ADDRESS(ROW(0),COLUMN(0)), gotten an error, look at ADDRESS help, and
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 ...

I've googled n googled n searched but can not find the answer so I thought I'd ask.

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 R[0]C[0], R*C*, A*1*, **, etc., assuming these notation are not used by Excel.

Thanks,

L Anderson

Thanks for the feedback

--
Regards,

Peo Sjoblom


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


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

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

--
Regards,

Peo Sjoblom


The answer I'm looking for is "ADDRESS(ROW(),COLUMN())". I had tried
ADDRESS(ROW(0),COLUMN(0)), gotten an error, look at ADDRESS help, and
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
...

I've googled n googled n searched but can not find the answer so I
thought I'd ask.

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 R[0]C[0], R*C*, A*1*, **, etc., assuming
these notation are not used by Excel.

Thanks,

L Anderson