Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
typing in a new cell and carrying the formatting of the previous c | Excel Worksheet Functions | |||
carrying previous totals from one worksheet to another. | Excel Worksheet Functions | |||
Carrying format with cell reference to another sheet. | Charts and Charting in Excel | |||
based on Cell/Column content ,cut one sheet's values and paste it in other sheet? | Excel Programming | |||
Adding new sheet's cell value to old formula | Excel Programming |