Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto-adjusting Formulas
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! -- Nick B. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto-adjusting Formulas
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 On Aug 19, 10:51*pm, Nick B. wrote: 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! -- Nick B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto adjusting for calculation when a row is added | Excel Discussion (Misc queries) | |||
Auto Adjusting Text Boxes? | New Users to Excel | |||
Auto Adjusting Text Box | Excel Discussion (Misc queries) | |||
Auto Adjusting # of rows between 2 worksheets | Excel Worksheet Functions | |||
Auto adjusting # rows between 2 worksheets | New Users to Excel |