Unmovable absolute reference
 

Hi, I'd like to be able to refer to a true, completely unmovable / static
absolute reference. My example:

I have a 2x6 range (six months in right-to-left, with a figure for each)
that is the source data for a simple bar graph. The current reference is
A1:B6. Each month I'd like to insert a new first column but keep the
reference A1:B6.

Currently Excel automatically changes the reference to A2:B7 when I insert
the column - even if I use $A$1:$B$6 to refer to the range. (Excel's help
files acknowledge that inserting a row will modify even absolute ranges.) Is
there any way to have a TRUE absolute range?

=INDIRECT("A1:B6")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mulkdog" wrote in message
...
Hi, I'd like to be able to refer to a true, completely unmovable / static
absolute reference. My example:

I have a 2x6 range (six months in right-to-left, with a figure for each)
that is the source data for a simple bar graph. The current reference is
A1:B6. Each month I'd like to insert a new first column but keep the
reference A1:B6.

Currently Excel automatically changes the reference to A2:B7 when I insert
the column - even if I use $A$1:$B$6 to refer to the range. (Excel's help
files acknowledge that inserting a row will modify even absolute ranges.)
Is
there any way to have a TRUE absolute range?

You can use the INDIRECT function, e.g.: =SUM(INDIRECT("$A$1:$B$6"))

mulkdog wrote:
Hi, I'd like to be able to refer to a true, completely unmovable / static
absolute reference. My example:

I have a 2x6 range (six months in right-to-left, with a figure for each)
that is the source data for a simple bar graph. The current reference is
A1:B6. Each month I'd like to insert a new first column but keep the
reference A1:B6.

Currently Excel automatically changes the reference to A2:B7 when I insert
the column - even if I use $A$1:$B$6 to refer to the range. (Excel's help
files acknowledge that inserting a row will modify even absolute ranges.) Is
there any way to have a TRUE absolute range?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html