REF error message in formula when inserting new rows

E

Ellen Dan

A REF error message is returned in a formula that references the cell above when a new row is inserted between. An example would be a running total based on the cell above plus a new value on the cell to the left.

If I want to add a new row and have it "join" the running total, I have to use the autofill option to refresh the formula. Is there any way around this?

I have tested this in both 2000 and 2002.
 
D

David McRitchie

Hi Ellen,
I have a page on the subject, the main thing that you want to do is
use OFFSET so that rows can be inserted and deleted.
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

Of course the macro is also oriented to this goal. You may get some
success from the option Copy Formats and Formula,
but required 3 or 5 identically formatted rows above usually doesn't
quite work for me. I have the option turn on though.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Ellen Dan said:
A REF error message is returned in a formula that references the cell above when a new row is inserted between. An example would
Click to expand...
be a running total based on the cell above plus a new value on the cell to the left.
 
Top