Reference changes when I insert new columns in the source sheet
 

I have absolute references set up to access data from a different sheet in
the same workbook. When I insert new columns in the data source sheet, the
absolute reference change to reflect the movement of the columns. I don't
want them to do that. How do I keep the absolute references absolute.

Reference changes when I insert new columns in the source sheet
 

You can use an INDIRECT function to give you the range, and as it is a
string within the function then it will not change when rows are
inserted.

Hope this helps.

Pete


On Mar 24, 3:23*pm, John Bare <John
wrote:
I have absolute references set up to access data from a different sheet in
the same workbook. *When I insert new columns in the data source sheet, the
absolute reference change to reflect the movement of the columns. *I don't
want them to do that. *How do I keep the absolute references absolute.

Reference changes when I insert new columns in the source sheet
 

Absolute reference means that the reference will always point to the same
cell(s). No matter what changes you make to the sheet in terms of inserting
rows or columns the reference will always poit to the original cell(s) you
specified. To do what you are asking requires using a formula such as
indirect. With indirect you create your cell reference in static text.

=Indirect("A1")
Will always return the value in cell A1. Note that this formula can not be
dragged to increment the reference. Additionally the formula is volatile so
if you have a lot of this type of formula your recacluation performance will
suffer.
--
HTH...

Jim Thomlinson


"John Bare" wrote:

I have absolute references set up to access data from a different sheet in
the same workbook. When I insert new columns in the data source sheet, the
absolute reference change to reflect the movement of the columns. I don't
want them to do that. How do I keep the absolute references absolute.

Reference changes when I insert new columns in the source sheet
 

Look up the INDIRECT worksheet function and see the advice under the example

RegMigrant

"John Bare" wrote:

I have absolute references set up to access data from a different sheet in
the same workbook. When I insert new columns in the data source sheet, the
absolute reference change to reflect the movement of the columns. I don't
want them to do that. How do I keep the absolute references absolute.