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

[Kai]

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!A11). 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?

 

[Pete_UK]

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&"'!A11"))

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

 

[Kai]

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.