Adding New Sheet, But Carrying Over Previous Sheet's Cell Calls
 

I have created a reporting workbook that currently takes reports
daily. However, I am limited to a certain number of days I am trying
to "sum" totals on. Sheet1 displays the of sum of last sheet directly
Sum(Sheet10!A1:D1). What I am trying to do is create a way I can keep
adding more sheets, but the call from Sheet1 updates to the latest
sheet added. Any ideas?

Adding New Sheet, But Carrying Over Previous Sheet's Cell Calls
 

One way (manual) is that each time you add a sheet then do Find/
Replace on all the cells in Sheet1 that contain a formula and change
Sheet10 to Sheet11, and so on.

Another way is to use a single cell on Sheet1 to record the name of
the last sheet added, eg put "Sheet10" (without the quotes) in cell
X1. The your formula above would become:

=SUM(INDIRECT("'"&$X$1&"'!A1:D1"))

Note the apostrophes are there to allow you to have spaces in the
sheet name.

Now if you add a new Sheet11, you just need to change the value in X1
to Sheet11 to get the formula to change.

Hope this helps.

Pete

On Oct 27, 5:58*am, Kai wrote:
I have created a reporting workbook that currently takes reports
daily. *However, I am limited to a certain number of days I am trying
to "sum" totals on. *Sheet1 displays the of sum of last sheet directly
Sum(Sheet10!A1:D1). *What I am trying to do is create a way I can keep
adding more sheets, but the call from Sheet1 updates to the latest
sheet added. *Any ideas?

Adding New Sheet, But Carrying Over Previous Sheet's Cell Calls
 

Pete,

Thank you for responding. I hoping to make the whole process
automated. I have actually figured out how to use the "names" to make
it add the value of said cell through out the workbook, by using a
SUM(Start:End!$A$1). Unfortunately I was unable to get it to
reference a name in that equation instead of a cell. But the direct
cell should work as well. I am now having issues with making the
activesheet call data from the previous sheet, if it was copied. That
one has me stumped.



On Oct 27, 8:08*am, Pete_UK wrote:
One way (manual) is that each time you add a sheet then do Find/
Replace on all the cells in Sheet1 that contain a formula and change
Sheet10 to Sheet11, and so on.

Another way is to use a single cell on Sheet1 to record the name of
the last sheet added, eg put "Sheet10" (without the quotes) in cell
X1. The your formula above would become:

=SUM(INDIRECT("'"&$X$1&"'!A1:D1"))

Note the apostrophes are there to allow you to have spaces in the
sheet name.

Now if you add a new Sheet11, you just need to change the value in X1
to Sheet11 to get the formula to change.

Hope this helps.

Pete

On Oct 27, 5:58*am, Kai wrote:



I have created a reporting workbook that currently takes reports
daily. *However, I am limited to a certain number of days I am trying
to "sum" totals on. *Sheet1 displays the of sum of last sheet directly
Sum(Sheet10!A1:D1). *What I am trying to do is create a way I can keep
adding more sheets, but the call from Sheet1 updates to the latest
sheet added. *Any ideas?