insert column without changing references

[Guest]

Hi - I have one sheet that references a range on another sheet. I want to
insert a column on the 2nd sheet and I don't want the cell references to
change on the first sheet (for example A5:J5 always contains current data,
when I get new data, I want to insert a new column A and have the old data
get 'bumped' to column K) Bit I have tried named ranges, absolute and
relative references and it always changes the formula on the first sheet.

 

[Don Guillett]

try using indirect

 

[Guest]

Hi
Don has already provided you an answer. However, just to be clear you use
indirect function like shown below

=SUM(INDIRECT("Sheet2!A5:J5"))

Alok