Forum Discussion

Ben O'Neill's avatar
Ben O'Neill
Copper Contributor
Oct 21, 2018

Copying sheets - updating formulas when you do

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 ...
excel
Formulas and Functions
VitalieCiobanu's avatar
VitalieCiobanu
MCT
Oct 21, 2018

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.

Resources