Copying sheets - updating formulas when you do

[Ben O'Neill]

I'm creating weekly reports where I want to pull data from the previous week to create monthly totals.

So to create new reports I copy the previous week's sheet so all my formatting and formulas are in place. I would like to set it up so that formulas will automatically relate to the previous sheet. 

For example in my week two report I will have a formula referencing the month-to-date from week 1. This will be week 1 + week 2 data. 

When I produce my week 3 report I would like to be able to add the previous month-to-date data to this week without recreating formulas. Is this possible? Essentially I want to duplicate week 2, to become week 3, but I want the formulas to reference week 2, where in week 2 they referenced week 1. 

[#2]

What logic/pattern are you using for naming your new sheets?

P.S. fyi, i'm traveling in few hours; may follow-up on this in a week.

[#3]

Here is an idea you can work with. Shortly, you need to automatically reference previous sheet name.

If you have a "numbered" pattern in naming your sheets (Sheet1, Sheet2 etc), you can extract current sheet number using RIGHT formula and then deduct 1 ( for Sheet1, Sheet2 etc example) and you will get your previous sheet's name.

Next, use INDIRECT to add the value in Sheet1!A1 (prev sheet) to the value in A1 from Sheet2 (current sheet).

Download attached file (preview will show errors) for more info. Duplicate last sheet and change the name accordingly and you will see how it works. In your last file, you can hide and protect those "helper cells" to avoid them being modified unwillingly.

Sorry for poor explanation (need to pack urgently), hope attached file will help understand the idea and move along from here. I'm sure it can be further streamlined from here, i just have no time now.