Formula "Change" Problem

[mholc3]

Situation:

In cell A1, I have a simple formula that sums the data in the following
6 horizontal cells.

The formula is =SUM(B1:G1)

I want this formula to never change under any circumstance.

The problem is if I insert a column before in front of column B the
formula changes to the following:

=SUM(C1:H1)

The spreadsheet is setup so that it is supposed to calculate a rolling
6 week total. Each new week a new column will be inserted and the data
for the new week entered.

How can i keep the formula from changing as the worksheet changes?

Thank you in adnvance to anyone that may be able to help.

 

[Laura Cook]

The following should work for you:

=SUM(INDIRECT("B1:G1"))

 

[Jazzer]

Hi,

Use INDIRECT function to keep a reference unchangeable, like this:

=SUM(INDIRECT("B1:G1"))

Problem in your example is that if you insert a column before this
formula, then it would create a circular reference (hence the function
is in B1 and B1 is part of the sum area)

- Asser