Auto sum different worksheets in workbook

**nigel_p** · 03-01-2009, 06:37 AM

Hi All,

Creating a stats program and I want the first weeksheet (i.e the first tab) to be a summary of all worksheets in workbook. With your help here already (Thanks heaps) I have completed the week to week data worksheet and saved as a template. I would now like to auto sum the same cells everytime I create a new templated worksheet to the first worksheet easily.

Is this possible? And How?

Thanks again in advance...you guys rock!!

**DonkeyOte** · 03-01-2009, 07:06 AM

I'm assuming given the sheets are templates they all have identical layouts and you are summing identical cells across all sheets... eg:

=SUM(Week1:Week10!A1)

If you want to adapt the above such that if Week11 is added it is included in the Sum the simplest method is to insert 2 new sheets into your file:

START
END

Insert START before Week1
Insert END after Week10

When you insert Week11 you simply insert between Week10 & END sheets... you can then adopt:

=SUM(Start:End!A1)

As you insert new sheets the above will encapsulate the new values automatically.

Does that help ?

**nigel_p** · 03-01-2009, 08:46 AM

Donkey - Thanks a million, problem solved

Once again, you guys rock!

**nigel_p** · 03-01-2009, 09:05 AM

Actually after playing I have found another issue, donkey you are right the data appears in the same cell in all worksheets due to the template created. I now want to chart a cell in seperate workbooks & keep getting "not in range" errors.

Can anyone help??

**Stuie** · 03-01-2009, 09:13 AM

Hi nigel_p,

What exactly do you mean by chart a cell?? sorry if im being thick. however if you are trying to link two seperate workbooks together then you need to include the workbook name in the forumla, like so:

=[Book1.xls]Sheet1!$B$5

This is just a basic example of getting the value from cell A1 from a workbook called Book1.

if the work book is not open in the same instance of excel then you need to specify the file directory as shown below:

='C:\MyFile\[Book1.xls]Sheet1'!$B$5

Hope this helps,

**nigel_p** · 03-01-2009, 09:26 AM

Thanks Stuie,

Actually my bad for not explaining well enough.

What I am trying to do is track the performance of one stat which appears at the same place on multiple worksheets within the same workbook. I know that I should be able to hold CTRL and click on the cell in the different worksheets, but I keep on getting error message (reference is not valid).

Cheers...