=OFFSET(cell("address"),-1,0)
"JJ" wrote:
What is wrong with this?
[....]
=OFFSET(cell("address"),-1,0)
I suppose you think that CELL("address") refers to the current
cell. But according to the CELL Help text: "If [Reference, the
second parameter, is] omitted, information specified in info_type
is returned for the last cell that was changed"(!).
So if you modify a random cell in the spreadsheet,
OFFSET(CELL("address"),...) anywhere in the spreadsheet
becomes relative to the modified cell. It changes all the time!
I want to be able to place this formula anywhere in
a spreadsheet and have it return the cell above it.
I used to think there should be a THISCELL() function, too.
But then I realized: if you simply enter the relative reference
to the cell containing the OFFSET() function, the cell name
will change automagically whenever you copy the formula.
For example, put =OFFSET(A2,-1,0) into A2; then copy and
paste it into B3. The formula becomes =OFFSET(B3,-1,0).
Does that satisfy your need? If not, why not? (Curious, not
provocative.)
|