Formula "Change" Problem

  • Thread starter Thread starter mholc3
  • Start date Start date
M

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.
 
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
 
Back
Top