Absolute references and inserting columns

B

BobK

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!
 
F

Fred Smith

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.
 
B

Bernard Liengme

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a sequence number(question number) to a paragraph which has a question and 4 options and answer 0
Permanently link formula references to Column Names and not Column Cell Numbers 1
Reference changes when I insert new columns in the source sheet 3
If then formula with multiple cells 2
Having Blank Cells, etc. ? 1
Formula autofill to cells below – formula arguments are cell areas in different columns 1
Need help with Excel Formula for calculating sum of products depending on criteria 3
Sumproduct absolute cell references 5

Top