Thread: How to reference a relative cell

How can I refer to a cell three to the left of the current cell ? I want to perform a calculation which remains valid even when I insert a column between the original data and the column which contains the calculation. Perhaps an example makes this clear:
A B C
1 5 3 8
2 3 9 12
Column C is adding the contents of the two columns to the left of it. I now want to add a new column (for illustration here named BB), and without altering column C, have column C remain as the sum of the two columns to its left:
A B BB C
1 5 3 6 9
2 3 9 1 10

Thanks
Eugene.

After I put the original 3 columns in, if I insert a column next to the total, it didn't include the new column in the total.
I also created a column someplace on the w/s and cut and pasted it into the column to the left of the total and it didn't alter the total.

Originally Posted by jmk

After I put the original 3 columns in, if I insert a column next to the total, it didn't include the new column in the total.
I also created a column someplace on the w/s and cut and pasted it into the column to the left of the total and it didn't alter the total.

That's exactly what happens to me too - do you know how to avoid that happening ? I _want_ the newly inserted column to be used in the function to its right.

An easy fix would be to add an extra column to the left of the column in which you have your totals. Keep this extra column empty but in your column of totals put the formula: =sum(A2:C2)

In this example you would have data in A2 and B2 but C2 is blank and D2 has the SUM formula

Now when you want to insert a column put it before the blank column (in this case C) and your summing column will adjust to include it.

Make the width of the empty column narrow so you'll remember it's there and it has a special purpose.