Relative reference to current cell?

[Brian Herbert Withun]

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

 

[Brian Herbert Withun]

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?

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[Rick Rothstein \(MVP - VB\)]

I've just come up with a better one:

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