Absolute References and Inserting columns
 

I've created a formula in column A that adds up some cells in column D. Ex:
=sum(D3:D6). If I insert one column in front of column D, the formula changes
to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6).
I also tried creating a range name called TEST referring to the original
$D$3:$D$6 range and the column insertion changed the TEST range definition as
well.

Is there a way to keep a formula from NOT adjusting to column insertions or
deletions? Thanks in advance!

Absolute References and Inserting columns
 

You need to use the INDIRECT function.

Instead of
=sum($D$3:$D$6)

use
=SUM(INDIRECT("$D$3:$D$6"))

Of course you won't be able to copy this formula down.

If you want that then you need to use ROW() and/or COLUMN() formulas to
build the reference...

"BobK" wrote:

I've created a formula in column A that adds up some cells in column D. Ex:
=sum(D3:D6). If I insert one column in front of column D, the formula changes
to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6).
I also tried creating a range name called TEST referring to the original
$D$3:$D$6 range and the column insertion changed the TEST range definition as
well.

Is there a way to keep a formula from NOT adjusting to column insertions or
deletions? Thanks in advance!