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!




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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question