Auto-adjusting Formulas

[Nick B.]

I created a balance sheet where column A is date formatted, column B is
general formatted, and columns C & D are both currency formatted. Line one
of the sheet does not contain any formulas, so that the beginning balance can
be adjusted.

My formula starts in cell D2, and is filled down the remainder of column D
in the worksheet. D2 formula is as follows: =IF(ISBLANK(C2),"",D1-C2)
D3 formula reads: =IF(ISBLANK(C3),"",D2-C3)
....and so on down the remainder of column D cells.

The problem is, once data is entered, if a line in the worksheet is deleted,
the following error occurs: #REF! (shows in all column D cells following
the deleted line). When the uppermost error cell is clicked on to review the
formula, the formula shows: =IF(ISBLANK(C3),"",#REF!-C3)
If the subsequent column D cells with the #REF! error are clicked on to
review their formulas, there is no #REF! within those formulas.

Any help is greatly appreciated!

 

[Pete_UK]

The solution is not to delete a row in your sheet !!

Suppose you delete row 10 - the formulae above this are not affected,
but the formula that was in row 11 would refer to a cell in row 10,
and when you delete that row this formula has lost the cell reference:
hence the #REF error. Subsequent formulae are trying to add onto the
previous row, but as this now contains an error then the cells further
down the sheet will also show the error, even though the formulae
themselves are not in error.

Hope this helps (to describe the problem).

Pete