Absolute references and inserting columns
 

I created a formula that contains absolute references (ex. =sum($C$5, $C$7,
$C$9)). I need to insert a NEW, blank column C. Even with absolute references
in the formula, the formula changes the references to $D$5, $D$7, etc.

Is there a way to keep the formula pointing to column C regardless of
inserting new columns?

Thanks in advance!

Absolute references and inserting columns
 

=SUM(INDIRECT("$C$5"),INDIRECT("$C$7"),INDIRECT("$ C$9"))
--
Gary''s Student - gsnu200833

Absolute references and inserting columns
 

Use the INDIRECT function.
--
David Biddulph

"BobK" wrote in message
...
I created a formula that contains absolute references (ex. =sum($C$5, $C$7,
$C$9)). I need to insert a NEW, blank column C. Even with absolute
references
in the formula, the formula changes the references to $D$5, $D$7, etc.

Is there a way to keep the formula pointing to column C regardless of
inserting new columns?

Thanks in advance!

Absolute references and inserting columns
 

One way is to use Indirect, as in:
=indirect("C5")+indirect("C7")+indirect("C9")

In your example, using Sum is superfluous. Just add the numbers.

Regards,
Fred.

"BobK" wrote in message
...
I created a formula that contains absolute references (ex. =sum($C$5, $C$7,
$C$9)). I need to insert a NEW, blank column C. Even with absolute
references
in the formula, the formula changes the references to $D$5, $D$7, etc.

Is there a way to keep the formula pointing to column C regardless of
inserting new columns?

Thanks in advance!

Absolute references and inserting columns
 

Why SUM when =$C$5 + $C$7+ $C$9 does the same thing?
Now to answer your question
=INDIRECT("C5")+INDIRECT("C7")+INDIRECT("C9")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"BobK" wrote in message
...
I created a formula that contains absolute references (ex. =sum($C$5, $C$7,
$C$9)). I need to insert a NEW, blank column C. Even with absolute
references
in the formula, the formula changes the references to $D$5, $D$7, etc.

Is there a way to keep the formula pointing to column C regardless of
inserting new columns?

Thanks in advance!