I have a large spreadsheet with 30 columns and over 700 lines. One section has to be added up down 6 columns. I am making constant alterations by adding new lines. Problem this seems to affect the addition formula which is always losing the top range of the columns. ie x2:x700 becomes x25:x700 how do I stop this.
A little tip when inserting rows in sum formulas is to always insert the new row above the formula row OR at row 2 of the range.
For example -
If you had a range of data from A1 to A9 and in A10 you had the formula =sum(A1:A9) then to insert a new row you would either click on the number 2 or 10, this way the formulas integrity would be maintained.
If it is important that the data that is in the new row should be at the top of the data block then modify the above by having a blank row at row 1, data in rows 2 to 9 and the formula in row 10 BUT the formula includes the blank row and the top insertion is still done on Row 2.
As VoG points out Absolute references should be used wherever practical, they will automatically update as you add in rows.
This thread is now locked and can not be replied to.