"Relative" Absolute Cell References in Excel 2007

[ridermark]

I have a worksheet that has a formula which uses absolute cell
references ($D$2:$F$2). If I insert a new column to the left of
Column D the "absolute" references change to ($E$2:$G$2).

What I want is to add data to the new column and have the formula
calculate on the original ($D$2:$F$2) range. Rather than changing the
formula every time I do this (and copying it down several hundred
rows) is there a way to get around this 'feature' and have the
absolute reference stay absolute?

Thanks for any insight,

Theoden

 

[Niek Otten]

Hi Theoden,

In some cell (let's say A1), put this text: D2:F2
Now use this formula (example is SUM):

=SUM(INDIRECT(A1))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have a worksheet that has a formula which uses absolute cell
| references ($D$2:$F$2). If I insert a new column to the left of
| Column D the "absolute" references change to ($E$2:$G$2).
|
| What I want is to add data to the new column and have the formula
| calculate on the original ($D$2:$F$2) range. Rather than changing the
| formula every time I do this (and copying it down several hundred
| rows) is there a way to get around this 'feature' and have the
| absolute reference stay absolute?
|
| Thanks for any insight,
|
| Theoden

 

[RagDyeR]

Try:

=SUM(INDIRECT("D2:F2"))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have a worksheet that has a formula which uses absolute cell
references ($D$2:$F$2). If I insert a new column to the left of
Column D the "absolute" references change to ($E$2:$G$2).

What I want is to add data to the new column and have the formula
calculate on the original ($D$2:$F$2) range. Rather than changing the
formula every time I do this (and copying it down several hundred
rows) is there a way to get around this 'feature' and have the
absolute reference stay absolute?

Thanks for any insight,

Theoden

 

[ridermark]

=SUM(INDIRECT("D2:F2"))

Thank you for the help. Amazing how easy it is when you know how!

 

[RagDyeR]

Thanks for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

=SUM(INDIRECT("D2:F2"))

Thank you for the help. Amazing how easy it is when you know how!