Excel 2007 : Formulas don't update when I delete or insert rows.

Hi,

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:

=SUM(L3,P3,Q3,W3,Z3)
=SUM(L4,P4,Q4,W4,Z4)
=SUM(L5,P5,Q5,W5,Z5)
etc.

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:

=SUM(AB2,AA3)
=SUM(AB3,AA4)
=SUM(AB4,AA5)
etc.

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.

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

Insertion problem:

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

Additional info:

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

Meredith