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?

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?

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?