I want to reference another cell in my worksheet in such a way that when I copy the sheet, I am still pointing to the cell on the original sheet. If the reference is to a cell on a different sheet, the sheet name is preserved when the sheet is copied but if the reference is within the same sheet, Excel changes the sheet name to the destination sheet when the sheet is copied.
I've tried defining a cell name, making its scope the whole workbook, and referencing the cell by name but, when I copy the sheet, Excel "helpfully" creates a duplicate name with scope of just the destination sheet.
I know I can use the "Indirect" function to reference a cell with a reference that will not change when the sheet is copied but I don't want to do that because it will fail if any structural change is made on the sheet containing the cell.
The application requires that successive copies be made of each sheet to track snapshots of the data over time but, for one aspect, I always want to refer back to a cell on the original sheet. The template for the workbook contains just the original sheet, waiting for initial data, which will then be copied to a new sheet for the next iteration of data. Can anyone help me with a suitable cell referencing technique to use that doesn't involve a re-design of the application.