Update cell references based on worksheet name

[shayward]

I am creating a spreadsheet which contains company data for each financial period in order to monitor the company progress. Currently I have a worksheet for period one containing formulas which refer to another worksheet where I have imported lots of data.

I would like to create worksheets for each subsequent period after this, so all of the formulas will stay the same, but each period I will import new data into a new worksheet.

For example, I currently have two worksheets called 'Period 1' and 'Period 1 data'. I would now like to create 'Period 2' and 'Period 2 data' worksheets. I would like to copy the 'Period 1' formulas but have them automatically update when I rename the worksheet to 'Period 2' so that they refer to the 'Period 2 data' worksheet rather than the 'Period 1 data' worksheet.

Any help would be appreciated.

 

[ExcelSpeedster]

You should be able to do this with a Find and Replace (Ctrl+H).

Once you've copied the sheets and renamed them "Period 2" and "Period 2 data" respectively, use Find and Replace to replace "Period 1" in each formula with "Period 2".