What is wrong with this? I want to be able to place this formula anywhere in
a spreadsheet and have it return the cell above it. Thanks!

=OFFSET(cell("address"),-1,0)

"JJ" wrote...
What is wrong with this? I want to be able to place this formula anywhere
in
a spreadsheet and have it return the cell above it. Thanks!

=OFFSET(cell("address"),-1,0)

Many things wrong with it. Most fundamentally, the 1st argument to OFFSET
must be a range reference, but the result of CELL("Address") is always a
string. "A1" isn't the same thing as A1. You could wrap CELL("Address")
inside an INDIRECT call, but there's no reason to use OFFSET and INDIRECT in
the same expression. You could use =INDIRECT("R[-1]C",0), which always
produces a reference to the cell above it, but if you're in cell X99, then
the easiest way to refer to the cell above is =X98. If your concern is
adapting to inserted or deleted rows, then you could avoid using the
volatile OFFSET or INDIRECT functions by using

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

More subtly, CELL("Address") returns the text address of the active cell as
of the last recalc. If you enter =CELL("Address") in cell D2, it'll return
"$D$2", but copy D2 and paste into E5, and *BOTH* cells D2 and E5 will
return "$E$5". I'll bet that's not what you want.

Thanks Harlan! I used the subtotal function and am going to use find/replace
to change all the formulas in one shot, this way I can grab the text in the
subtotal line as well as subtotals. Thanks again!

"Harlan Grove" wrote:

"JJ" wrote...
What is wrong with this? I want to be able to place this formula anywhere
in
a spreadsheet and have it return the cell above it. Thanks!

=OFFSET(cell("address"),-1,0)

Many things wrong with it. Most fundamentally, the 1st argument to OFFSET
must be a range reference, but the result of CELL("Address") is always a
string. "A1" isn't the same thing as A1. You could wrap CELL("Address")
inside an INDIRECT call, but there's no reason to use OFFSET and INDIRECT in
the same expression. You could use =INDIRECT("R[-1]C",0), which always
produces a reference to the cell above it, but if you're in cell X99, then
the easiest way to refer to the cell above is =X98. If your concern is
adapting to inserted or deleted rows, then you could avoid using the
volatile OFFSET or INDIRECT functions by using

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

More subtly, CELL("Address") returns the text address of the active cell as
of the last recalc. If you enter =CELL("Address") in cell D2, it'll return
"$D$2", but copy D2 and paste into E5, and *BOTH* cells D2 and E5 will
return "$E$5". I'll bet that's not what you want.

why not just use

=OFFSET(H20,-1,0)

in H20

--

HTH

RP

"JJ" wrote in message
...
What is wrong with this? I want to be able to place this formula anywhere
in
a spreadsheet and have it return the cell above it. Thanks!

=OFFSET(cell("address"),-1,0)

Everything Harlan said and...
Why not, if you're in cell H5, make a formula that says =H4
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"JJ" wrote:

What is wrong with this? I want to be able to place this formula anywhere in
a spreadsheet and have it return the cell above it. Thanks!

=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.)

"
wrote...
....
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"(!).
....

A fine example of an error in online help. Enter =CELL("Address") in A1.
Copy A1 and paste into A2, then move to B3 and press [F9]. Did you *change*
B3?

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!
....

More accurately, it becomes relative to the active cell at the time of the
most recent recalc.