# Thread: Summarise identical sheets

1. I have created a workbook with 31 identical data entry sheets, 1 for each day of the week. The format is quite messy, and has to be that way, there are many formulas and calculations. I need to create a 32nd summary sheet, identical to the others that summarises the entire month.

I'm looking for an easy way to achieve this without manually calculating each one. Any suggestions? Thanks

2. [quote name='VegasNath' post='769127' date='03-Apr-2009 22:41']The format is quite messy, and has to be that way[/quote]
I'd much prefer to use a single sheet with a database-like table. This makes is a lot easier to summarize the data in various ways.

But you can use formulas like this to calculate across many sheets:

=SUM('Sheet1:Sheet31'!B2)

3. I completely agree, this is very messy, and in my eyes far from ideal! Thanks Hans.

4. Hans,
When I copy and paste the formula into the formula bar for cell B2 on sheet 32, the formula converts to =SUM('Sheet1:[Sheet31]Sheet31'!B2) and shows #ref.

Am I missing something?

5. In the first place, think about where you want a total - the B2 in my example was just that: an example.
But let's take B2 for illustration purposes.

- Activate the summary sheet.
- Select cell B2.
- Start a formula by typing =SUM(
- Click on the sheet tab of the first day.
- You should see something like =SUM(Sheet1! where Sheet1 is the name of the sheet.
- Hold down the Shift key and click on the sheet tab of the last day.
- You should see something like =SUM(Sheet1:Sheet31!
- Click on cell B2.
- You should see something like =SUM(Sheet1:Sheet31!B2
- Type the closing parenthesis ).
- You should see something like =SUM(Sheet1:Sheet31!B2)
- Press Enter or click the green check mark in the formula bar.

6. Thanks, I figured it out shortly after posting: >> =SUM('1:31'!H9), copied and pasted to all the required cells and then just changed the cell reference for each.

Cheers Hans.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•