Carry data from sheet to sheet automatically
I have to create a new sheet in a workbook every week. The sheet contains
repetitve formulae so I can copy and paste the entire range from say sheet 2 to my new sheet 3, but I have a running total function that has to carry through from sheet to sheet throughout the year. When I copy and paste the formulae from sheet 2 to my new sheet 3, the cell(s) containing the running total formulae are still looking to sheet 1 (as it as supposed to do when it was in sheet 2, but now it's in sheet 3, I want it to look at sheet 2 instead). Obviously I can update the formula manually to look at last week's total in sheet 2 but I have to do this every week. Is there a way to get the new sheet 3 to refer back to sheet 2 rather than sheet 1 automatically? |
Carry data from sheet to sheet automatically
There is nothing automatic but you could look into using Indirect which would
allow you to put the desired sheet name into a specific cell and all of the formulas would reference that cell to determine the appropriate sheet. Note that Indirect is volatile so if you are going to have thousands of these formulas then you could see a performance hit. -- HTH... Jim Thomlinson "Andrew Ball" wrote: I have to create a new sheet in a workbook every week. The sheet contains repetitve formulae so I can copy and paste the entire range from say sheet 2 to my new sheet 3, but I have a running total function that has to carry through from sheet to sheet throughout the year. When I copy and paste the formulae from sheet 2 to my new sheet 3, the cell(s) containing the running total formulae are still looking to sheet 1 (as it as supposed to do when it was in sheet 2, but now it's in sheet 3, I want it to look at sheet 2 instead). Obviously I can update the formula manually to look at last week's total in sheet 2 but I have to do this every week. Is there a way to get the new sheet 3 to refer back to sheet 2 rather than sheet 1 automatically? |
Carry data from sheet to sheet automatically
Thanks Jim, I don't know anything about Indirect so it looks like I'll have a
busy few lunch breaks "playing" with a new toy! "Jim Thomlinson" wrote: There is nothing automatic but you could look into using Indirect which would allow you to put the desired sheet name into a specific cell and all of the formulas would reference that cell to determine the appropriate sheet. Note that Indirect is volatile so if you are going to have thousands of these formulas then you could see a performance hit. -- HTH... Jim Thomlinson "Andrew Ball" wrote: I have to create a new sheet in a workbook every week. The sheet contains repetitve formulae so I can copy and paste the entire range from say sheet 2 to my new sheet 3, but I have a running total function that has to carry through from sheet to sheet throughout the year. When I copy and paste the formulae from sheet 2 to my new sheet 3, the cell(s) containing the running total formulae are still looking to sheet 1 (as it as supposed to do when it was in sheet 2, but now it's in sheet 3, I want it to look at sheet 2 instead). Obviously I can update the formula manually to look at last week's total in sheet 2 but I have to do this every week. Is there a way to get the new sheet 3 to refer back to sheet 2 rather than sheet 1 automatically? |
All times are GMT +1. The time now is 09:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com