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

**mdomzalski** · 01-19-2012, 01:31 PM

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

**mdomzalski** · 01-19-2012, 01:48 PM

One piece of additional info: I noticed that only some of my auto sum totals at the ends of columns for other data did not calculate correctly when I deleted rows. Some of them automatically adjusted. For instance the auto sum changed from =SUM(P3;P18) to =SUM(P3;P5) when I deleted rows, but it gave me a reference error in another column with the same formula.

**FDibbins** · 01-19-2012, 08:50 PM

its hard to say from what you have said, but it looks like you may have included the cell that the formula is in...in the formula itself. Would it be possible to include a sample spreadsheet with some dummy data, and indicate what you are trying to do when you add/delete rows? and just for reference, letters indicate columns, numbers indicate rows, so AA would be col AA

**mdomzalski** · 01-20-2012, 12:28 PM

Sorry, I meant to say column in that part of the post, and I'll edit it. I am referring to deleting/inserting rows and not columns though. I did not include the cell the formula is in in the cell itself. If I did that, there would have been an error even before I tried to insert or delete any rows, right? Below are the formulas I used in column AB. I'll list the cell number first and then the formula that appears in the cell without any insertions/deletions of rows.

Cell AB3: =SUM(AB2,AA3)
Cell AB4: =SUM(AB3,AA4)
Cell AB5: =SUM(AB4,AA5)
Cell AB6: =SUM(AB5,AA6)

So the formula I'm using should add the cell above and the cell to the left of the cell where the formula is located.

This problem appears to go beyond just this column and set of formulas, however, since I also even a simple auto sum to auto correct in a different column that did not update when I deleted rows. This is what I was referencing in my second comment, quote below:

One piece of additional info: I noticed that only some of my auto sum totals at the ends of columns for other data did not calculate correctly when I deleted rows. Some of them automatically adjusted. For instance the auto sum changed from =SUM(P3;P18) to =SUM(P3;P5) when I deleted rows, but it gave me a reference error in another column with the same formula.

**OnErrorGoto0** · 01-20-2012, 12:31 PM

=SUM(INDEX(AB:AB,ROW()-1),INDEX(AA,ROW()))
I think will do what you need.

**mdomzalski** · 01-24-2012, 01:55 PM

Sorry, I didn't see this until today. It didn't appear to work, but thanks for your help OnErrorGoto0. I copied and pasted the formula into cell AB3 and got the error #NAME?. When I click on the dialogue box and trace the error, I get the message, "The Error Tracer encountered existing arrows or a circular reference. Delete all tracer arrows or resolve the circular reference, and then choose Trace Error again."

I'm not clear on the formula you typed, and I haven't used INDEX before. I fiddled around with putting values in the parenthesis after row and then both there and right before the colon because I thought that might be the problem, but it didn't work. (However, I'm not sure which ones I'm supposed to put.) Could you explain that formula to me and what it means? Then I might be able to figure out where to put in the values I need to make it work.

Just to be clear, in case there is still confusion, I'm not trying to refer to the cell I'm in. I want the cell I'm in to add the value of the cell above with the value of the cell to the left. Cell AB3 will be the sum of cells AB2 and AA3; Cell AB4 will be the sum of AB3 and AA4; etc.

In other words the formulas in column AB should be able to do what I have below. If I need a different formula in order to do this and have it update when I add or delete rows, that is fine. Right now it does actually work, just not when I add or delete rows.

AB3 =SUM(AB2,AA3)
AB4 =SUM(AB3,AA4)
AB5 =SUM(AB4,AA5)
AB6 =SUM(AB5,AA6)

My formula works, it just doesn't update. If the indexing thing above will allow these things to update with some minor tweaks, that would be great. Right now though, I can't get that formula to work at all.

**OnErrorGoto0** · 01-24-2012, 02:31 PM

You could use
=indirect("rc[-1]",0)+indirect("r[-1]c",0)

**mdomzalski** · 01-24-2012, 05:06 PM

Before I try that formula, could you explain it to me along with the other formula you suggested? Thanks, Meredith

**OnErrorGoto0** · 01-24-2012, 05:16 PM

I will try

INDIRECT takes a cell reference as text, so it is not affected by deletion/insertion of cells. It can take the reference in either A1 style or R1C1, which is what I have used here
"RC[-1]"
refers to the same row as the formula is in, and one column to the left.
"r[-1]c"
refers to the row above the one the formula is in, but the same column.

So it adds the cell to the left of the formula cell to the cell above it and is unaffected by any insertion or deletion.

The previous formula (it should have been AA:AA rather than just AA, I have just noticed) used INDEX which returns a value from a table (it can be a one row or one column table too) given a row and/or column position.
So
INDEX(AA:AA,ROW())
refers to the cell in column AA on the same row as the formula, and INDEX(AB:AB, row()-1) refers to the cell above the formula cell in column AB. The would only be affected by you deleting the whole of column AA or AB. (If you delete the row the formula is in, it is not a problem since other formulas only refer to their own row numbers).

Does that explain it?