Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
JustMe
 
Posts: n/a
Default 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


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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
  #3   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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



  #4   Report Post  
Andy Wiggins
 
Posts: n/a
Default

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




  #5   Report Post  
JustMe
 
Posts: n/a
Default

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




  #6   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Enter an Excel cell reference as part of a custom header/footer Suegi123 Excel Worksheet Functions 1 April 1st 05 10:55 PM
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. [email protected] Excel Worksheet Functions 2 December 11th 04 01:05 AM
Paste rows of numbers from Word into single Excel cell BecG Excel Discussion (Misc queries) 1 December 8th 04 05:55 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
name of another worksheet in cell for reference Tom A Johnson Excel Worksheet Functions 2 November 12th 04 12:28 AM


All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"