[Bobbie Weeks (Ms)]

This is probably very easy but as I don't know what it's called, I'm
not sure how to find out how to do it in the group.

I want to find out how to stop the cell reference in a formula
changing when I insert a column. For example, say I have a formula in
A1 that says =SUM(B1+C1+D1) and in B1 I have 1, C1 I have 2, and D1 I
have 3, so it reads:

A - B - C - D
6 - 1 - 2 - 3

What I want to do is insert a column between C and D so it becomes a
new D and the old D moves to E, but the formula knows the column has
been inserted and changes itself automatically to =SUM(B1+C1+E1). Is
there anyway to stop the formula doing that and to always look at
=SUM(B1+C1+D1) regardless of insert of columns? So that it looks at
the new figure in D and will use that.

Does this make sense?

[Rick Rothstein \(MVP - VB\)[_576_]]

You can do it this way...

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

Rick


"Bobbie Weeks (Ms)" wrote in message
...
This is probably very easy but as I don't know what it's called, I'm
not sure how to find out how to do it in the group.

I want to find out how to stop the cell reference in a formula
changing when I insert a column. For example, say I have a formula in
A1 that says =SUM(B1+C1+D1) and in B1 I have 1, C1 I have 2, and D1 I
have 3, so it reads:

A - B - C - D
6 - 1 - 2 - 3

What I want to do is insert a column between C and D so it becomes a
new D and the old D moves to E, but the formula knows the column has
been inserted and changes itself automatically to =SUM(B1+C1+E1). Is
there anyway to stop the formula doing that and to always look at
=SUM(B1+C1+D1) regardless of insert of columns? So that it looks at
the new figure in D and will use that.

Does this make sense?