Remember Me?

 #1     December 28th 07, 04:29 PM posted to microsoft.public.excel.worksheet.functions "Relative" Absolute Cell References in Excel 2007 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

#2
December 28th 07, 04:52 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,440
"Relative" Absolute Cell References in Excel 2007

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

wrote in message ...
|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

#3
December 28th 07, 05:00 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,572
"Relative" Absolute Cell References in Excel 2007

Try:

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

HTH,

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

wrote in message
...
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

 #4     December 28th 07, 05:21 PM posted to microsoft.public.excel.worksheet.functions "Relative" Absolute Cell References in Excel 2007 =SUM(INDIRECT("D2:F2")) Thank you for the help. Amazing how easy it is when you know how!
#5
December 29th 07, 04:48 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,572
"Relative" Absolute Cell References in Excel 2007

Thanks for the feed-back.
--

Regards,

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

wrote in message
...

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

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Garnet Excel Discussion (Misc queries) 3 February 22nd 07 08:51 AM Claude S. Weiller Excel Discussion (Misc queries) 1 August 3rd 06 08:56 PM pfrank Excel Discussion (Misc queries) 6 June 28th 06 10:32 PM jwk515 Excel Discussion (Misc queries) 3 May 9th 06 02:00 AM java Excel Worksheet Functions 2 July 12th 05 09:53 PM

All times are GMT +1. The time now is 01:15 PM.