Maintaining formula when adding new column

Topic

New Reply

I hope I am describing this accurately:

Column M (April 2009)
Column N (May 2009)
Column O (formula) =SUM(N6-M6)

For June, I copy Column N and insert it to the right of Column N (becoming Column O )
Now Column P has the formula =SUM(N6-M6) which I have to manually change to =SUM(O6-N6)

Is there an option where the formula automatically updates without manually having to change it?

Thank you for your help.

Michael

Reply | Quote

Replies

You could use a formula like this in O6:

=OFFSET(O6,0,-1)-OFFSET(O6,0,-2)

This will always calculate the difference of the two cells to the left of the cell with the formula. Note that there is no need to use SUM.

Reply | Quote

Thank you Hans – EXACTLY what I was looking for.

Michael

Reply | Quote