Absolute sell references

[Guest]

Morning,
I currrently have a spreadsheet with the following formula =sum($E2:$V2)

I insert a column and shift all columns from E-V to the right (makeing them
F-W). I would like the formula to remain =sum($E2:$V2) but it keeps chagning
to =sum($F2:$W2). Is there anyway to make a cell reference stay put no
matter what or will it always move when you insert columns inside the range?

 

[Guest]

Hi,

One way:

=SUM(INDIRECT("E"&ROW(A2)&":V"&ROW(A2)))

HTH
Jean-Guy

 

[Guest]

Put an apostrophe to the left of the equal sign: '=sum($E2:$V2)

This converts the formula to a text string. Then insert your columns. Go
back to the text string, delete the apostrophe and hit enter. Note that if
calculation is set to manual you will have to re-force calculation after
doing this.

Dave

 

[Earl Kiosterud]

Jedi,

Try =SUM(INDIRECT("E2:V2"))

 

[Guest]

Thanks to everyone for the quick response

Jedi,

Try =SUM(INDIRECT("E2:V2"))