Sheet references does not change relatively

[jluqaz]

Dear Sirs, I created a new spreadsheet. In the new spreadsheet I have Sheet 1, Sheet 2 and Sheet 3. I put the workings on Sheet 1 and put the constants I was using in Sheet 2. Sheet 3 was left blank. I renamed Sheet 2 to Constants. I referred to the constants with absolute address - Constants.$B$2, etc.

I have to create multiple Sheet 1 for different cities. I insert a new sheet after Sheet 1. New Sheet was automatically assigned Sheet 4. Sheet 3 is still blank. I highlighted Sheet1 (mouse over cell 0,0 / click to highlight the sheet), right-click / copy, moved to Sheet 4, right-click / paste. All the cell references to Sheet 2 (Constants) was showing ERR...

Upon checking it was found that during the cut and paste operation, the Sheet was not "updated" relatively, leaving the Constants.$B$2 reading Sheet3.$B$2...

I believe OO got confused...

thanks.

[davidh182]

I think you need
$Constants.$B$2

Without the first $ it will refer to the sheet in the same relative position as "Constants" is to sheet 1.