Relative reference to current cell?
 

Is there a simple way for a cell formula to refer to its own cell?

I can get my own column by using =COLUMN(), I can get my own row by
using =ROW(). How can I get my own CELL ?

I like to use C1=OFFSET(C1,-1,0) as a cut/copy/paste/delete-safe
formula which refers to the value immediately above the current cell.
The only trouble is in discussing formulas with others. I cannot say
"use =OFFSET(B44,-1,0)" without further saying that that formula will
only work in cell B44.

Is there an equivalent to =OFFSET(ME(),-1,0) ?

I have tried =OFFSET(,-1,0) but that is a syntax error.

The best I have yet come up with is the following:

=INDIRECT(CONCATENATE("R",TEXT(ROW()-1,0),"C",TEXT(COLUMN(),0)),0)

Can anyone do it better?

Brian Herbert Withun

Relative reference to current cell?
 

On Nov 7, 2:51 pm, Brian Herbert Withun wrote:
Is there a simple way for a cell formula to refer to its own cell?

I can get my own column by using =COLUMN(), I can get my own row by
using =ROW(). How can I get my own CELL ?

I like to use C1=OFFSET(C1,-1,0) as a cut/copy/paste/delete-safe
formula which refers to the value immediately above the current cell.
The only trouble is in discussing formulas with others. I cannot say
"use =OFFSET(B44,-1,0)" without further saying that that formula will
only work in cell B44.

Is there an equivalent to =OFFSET(ME(),-1,0) ?

I have tried =OFFSET(,-1,0) but that is a syntax error.

The best I have yet come up with is the following:

=INDIRECT(CONCATENATE("R",TEXT(ROW()-1,0),"C",TEXT(COLUMN(),0)),0)

Can anyone do it better?

Brian Herbert Withun

I've just come up with a better one:

=OFFSET($A$1,ROW()-2,COLUMN()-1)

but is this the simplest form?

Relative reference to current cell?
 

The best I have yet come up with is the following:

=INDIRECT(CONCATENATE("R",TEXT(ROW()-1,0),"C",TEXT(COLUMN(),0)),0)

Can anyone do it better?

Brian Herbert Withun

I've just come up with a better one:

=OFFSET($A$1,ROW()-2,COLUMN()-1)

but is this the simplest form?

You could have made your initial formula better to look at by using the
ampersand to concatenate your text together instead of using the CONCATENATE
function....

=INDIRECT("R"&ROW()-1&"C"&COLUMN(),0)

Another possibility using INDIRECT is this...

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

Which of the 3 formulas is "better"? Being they are all volatile, I have no
idea.

Rick

Relative reference to current cell?
 

I've just come up with a better one:

=OFFSET($A$1,ROW()-2,COLUMN()-1)

but is this the simplest form?

You could have made your initial formula better to look at by using the
ampersand to concatenate your text together instead of using the
CONCATENATE function....

=INDIRECT("R"&ROW()-1&"C"&COLUMN(),0)

Another possibility using INDIRECT is this...

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

Which of the 3 formulas is "better"? Being they are all volatile, I have
no idea.

Although I don't believe this formula is volatile...

=INDEX(1:65536,ROW()-1,COLUMN())

Rick