Remove Circular Reference

[nd4spd]

A B C D E
1 2 3 4 10 =SUM($A2:OFFSET(E2,0,-1))

The formula above seem to work and does work for me as I do have to add or remove columns after column A without messing with with the total formula but I seem to be getting Circular References n i cant seem to correct it any help appreciated. thanks

[:) Sixthsense :)]

On which cell you have applied that formula? The formula is supposed to be used in E2 Cell.

[martindwilson]

works just fine what cell are you putting it in?

[nd4spd]

Formula is in E2

[nd4spd]

screen shot

[:) Sixthsense :)]

I don't get any circular reference error in your attached file. Refer the attached screenshot for reference.

Check whether the calculation is marked as Manual. Because the circular reference error message is replacing the calculate notification.

[nd4spd]

Calculations are marked as Automatic how weird is this !!!!

[L-Drr]

Just put =SUM(A2:D2) in E2. The range will change along with adding or removing columns.

[nd4spd]

Sixthsense was meaning to upgrade to office 2010 let me try n see if it solves the issue.


L-Drr you would have to change the formula if you added a column just before the last column.

[:) Sixthsense :)]

Version is not an issue here... To make it clear I have attached the 2007 screenshot too...

[L-Drr]

L-Drr you would have to change the formula if you added a column just before the last column.

You can also put the formula in F2 and add the empty E2 to the sum range. Empty cells are summed as 0 and then you never have to insert a new column at the end of the range.

[nd4spd]

Sixthsense I upgraded to 2010 and it has removed circular references.. just wonder if my office was corrupt or was it paranormal cant comment !!!

thanks all