Absolute sell references
 

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?

Absolute sell references
 

Hi,

One way:

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

HTH
Jean-Guy

"PowerPoint Jedi" wrote:

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?

Absolute sell references
 

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"PowerPoint Jedi" wrote:

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?

Absolute sell references
 

Jedi,

Try =SUM(INDIRECT("E2:V2"))
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"PowerPoint Jedi" wrote in
message ...
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?

Absolute sell references
 

Thanks to everyone for the quick response

"Earl Kiosterud" wrote:

Jedi,

Try =SUM(INDIRECT("E2:V2"))
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"PowerPoint Jedi" wrote in
message ...
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?