An absolute reference is changing!!!!

[smithgt]

I have a formula on sheet1 referging to column H on sheet2.

The reference is

Sheet2!$H:$H,

If in Sheet2 I insert a new column (before H) the formula on Sheet1 changes to

Sheet2!$I:$I,

Is there a way to prevent this?

 

[VoG]

Try

=INDIRECT("Sheet2!$H:$H")

 

[smithgt]

Thanks

 

[shg]

Or for a non-volatile alternative,

INDEX(Sheet2!$1:$1048576, 0, 8)

In Excel 2003-

INDEX(Sheet2!$1:$65536, 0, 8)