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.