Thread: Automatically Updating Data from New Worksheet

I am using Excel 2003. I have created a workbook with multiple sheets for tracking data on a weekly basis. Each sheet is the same, same rows, same headings, just different data coming in (tracking sales, hours, etc). The workbook is being used by a manager that is not familiar with Excel. I have created it so all she needs to do is copy/paste a new worksheet for each new weeks data.

What I want to figure out is how to create a summary sheet at the beginning of the workbook that will automatically update the data that is currently populated, but will also update new data that is added when she creates a new worksheet.

I am not an expert with excel, and have yet to use any VBA solution (in fact, I just today learned that term, if, indeed I am using it correctly). If there is a better way to do what I am doing, I'm very open to suggestions.

The reason I used this set up for my manager is that it also includes graphs for each week, so she can "see" the data better. The copy/move worksheet works great for creating a brand new worksheet with the graphs I put into them, so when she populates the data, she can see the results immediately.

It is while since I used Excel 2003 so this solution may not work but is worth trying. It is possible to sum (or use other aggregate functions such as COUNT) through adjacent sheets. Say you have 4 sheets and want the summary on Sheet1:A1 for cells A1 in sheets 2 to 4,
Enter (without the quote) "=SUM(" in Sheet1:A1,
Point to cell A1 on Sheet1,
Hold down the shift key and click on the tab of sheet 4 - you should see "=SUM(Sheet2:Sheet4!A1"
Add the end bracket and you will have the sum of the cells.

Any sheet now added between Sheet2 and Sheet4 will be included in the sum.

In practice you need to ensure that new entries go between the start and end sheet (which could be blank). The sheet order would be, left to right,
Summary Sheet
Start Sheet
Data Sheet 1
Data Sheet 2
...
Data Sheet N
End Sheet

There is a way to help prevent user error given on http://www.ozgrid.com/News/excel-validation-tips.htm
but I haven't tried it.

My experience of summing through sheets is that it is only useful if the structure of the sheets is fixed. Any modification can mean a lot of reworking of summation formulae,

I am going to try that solution. My data cells location and name do not change from sheet to sheet, so hopefully there won't be any reworking needed.

This is what I ended up trying from your post: =SUM('First Page'!B4:'Last Page'!B4)
and it's giving me a "VALUE" error.

Originally Posted by Domski

The formula would actually be; =SUM('First Page:Last Page'!B4)

Dom

Yep, that did it. Thank you.