Copying Sheets

[Stacy C]

Good afternoon,

I am working on a spreadsheet, where I am creating copies of a master page
to use for each month of the year. As an example:

Sheet 1 & 2 will be copied to Sheet 3 & 4, where sheet 4's formulas will
need to reference sheet 3.

I know when I copy, all of the formulas will reference the original sheet.
Is there an easier way to have Sheet 4's formulas automatically reference
Sheet 3 (rather than Sheet 1), than to go in and manually edit the formulas
on the sheet itself?

Thank you for any advice.

 

[smartin]

Good afternoon,

I am working on a spreadsheet, where I am creating copies of a master page
to use for each month of the year. As an example:

Sheet 1 & 2 will be copied to Sheet 3 & 4, where sheet 4's formulas will
need to reference sheet 3.

I know when I copy, all of the formulas will reference the original sheet.
Is there an easier way to have Sheet 4's formulas automatically reference
Sheet 3 (rather than Sheet 1), than to go in and manually edit the formulas
on the sheet itself?

Thank you for any advice.

You will have to edit the formulas, but you can make this easy. On sheet
4, just do a find and replace 'sheet 1' with 'sheet 3'.

 

[Stacy C]

I inadvertently found out that it is possible to copy a series of sheets and
have the formulas automatically update to reference the correct [new] sheet.

To do this, simply select the series of sheets you want to copy (in my case,
Sheet 1 & Sheet 2), and copy them together (ctrl+click to select more than
1). When I copied the sheets over, it automatically adjusted the formulas on
the new sheets to reference themselves, and not the originals.

 

[smartin]

Good find! Thanks for sharing.

I inadvertently found out that it is possible to copy a series of sheets and
have the formulas automatically update to reference the correct [new] sheet.

To do this, simply select the series of sheets you want to copy (in my case,
Sheet 1 & Sheet 2), and copy them together (ctrl+click to select more than
1). When I copied the sheets over, it automatically adjusted the formulas on
the new sheets to reference themselves, and not the originals.

You will have to edit the formulas, but you can make this easy. On sheet
4, just do a find and replace 'sheet 1' with 'sheet 3'.