Reference changes when I insert new columns in the source sheet

[John Bare]

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.

 

[Pete_UK]

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

 

[Jim Thomlinson]

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.

 

[Reg]

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

RegMigrant