I have an worksheet that calculates commissions and bonuses that has a running total in the last column. I
have a column of subtotals before this one that calculates different types of payments. (The running total is for the
purposes of checking against the statements to be sure everything is correct.)
When I created the spreadsheet, I
used the auto fill to copy the formula down the column. From what the little I know about absolute references, it doesn't
look like I have created any, so it should update, right? I don't want to have to set up my formulas all over again for the
new sheets. I want to clear/delete/add rows while keeping formulas and want the ability to add rows without either dragging
or manually entering formulas.
Here is how I have it set up. Column AA is the column of subtotals. I have it set
to add up the different types of payments that are received on each statements. The formulas for column AA is as follows:
Here is how I set up column AB. (The data starts in row 3, and row 2 does not contain any values.) Starting
on cell AB3:
I am having trouble keeping my formulas both when I delete the contents of cells/rows and when I try to
insert rows. I would like to create a new workbook based on the old one and copied the sheet to a new book. I'm outlining the
two different issues below:
Deletion/clear contents problem:
When I delete a row, I get a cell
reference error. Example, if I delete row 4, the formula in cell AB4 is =SUM(#REF!,AA4). That cell and the rest of the cells
then show #REF!. The formulas in the other cells have the correct references. So cell AB5 is =SUM(AB4,AA5). I know #REF!
error in AB5 is there because it can't reference cell AB4, but it's odd that it updates and doesn't display the formula in
one of these ways: =SUM(#REF!,AA5), or =SUM(#REF!,AA6), or =SUM(AB5,AA6). I know I can fix this by entering the right cell
where it says #REF!, but I want it to auto update so I don't have to do this each time I create a new sheet.
I delete the contents of a cell, the formulas disappear. If I clear the contents of row 4 and want to update the formula, I
have to drag the formula all over again. Thankfully, for this one, the formulas in the following rows do not change.
When I insert a row, formulas are not updated. For example, if I insert a row above row
4, there is no formula in the new row; the formula in the new cell AB4 is blank. [I want it to update to be =SUM(AB3,AA4).]
Also, the formula in the cell that was moved down does not update properly. It should update to be =SUM(AB4,AA5), but instead
it reads =SUM(AB3,AA5).
Things seemed to update okay on this computer yesterday, but
I don't recall if I tested these actions. When I sent the document to myself and opened it on another computer, that's when I
had these problems with the formulas. (We're not networked.) I made some minor changes and sent the document to myself again
at this computer, and I'm having the same problem. I don't know if there is a setting or something that I need to change or
what, but I need some help.
Sorry for the long post, and thank you in advance for any help/advice you can