Summarise multiple sheets

**dreams1** · 06-05-2013, 01:40 PM

Hi,

I have multiple sheets which i would like to summarise.

To explain further, there will be 30 sheets for each day of the month with list of staff and the hours they worked for different category (overtime, normal hours, training hours, sickness hours etc). I would like to summarise all these to a page which shows total for each staff for each category eg if staff A worked 3 hours on day 1 and then 8 hours on day 15, the summary page should show 11 hours under category 'overtime' for staff A.

I also try to explain as below. Thank you in advance.

sheet 1 Hours 1 Hours 2 Hours 3
staff A 30 15 10
staff B 20 10 20
Staff C 10 30 30

sheet 2 Hours 2 Hours 5 Hours 6
staff A 5 3 3
Staff C 8 5 6
Staff D 5 8 8

sheet 3 Hours 1 Hours 4 Hours 5
Staff C 45 34 33
Staff D 2 65 45
Staff E 6 78 50

Summary sheet Hours 1 Hours 2 Hours 3 Hours 4 Hours 5 Hours 6
staff A
staff B
Staff C
staff D
Staff E

**arlu1201** · 06-05-2013, 02:18 PM

You can do this in many ways - one way being consolidate all the data into one file and then create a pivot. It will give you the summary.

**dreams1** · 06-05-2013, 02:45 PM

Originally Posted by arlu1201

You can do this in many ways - one way being consolidate all the data into one file and then create a pivot. It will give you the summary.

Is there a way to automate this function by using formula? I have 30 sheets and both staff and categories changes for each sheet/day, and I want to review the summary page every day (eg to see who has spent how many hours on overtime month to date?). Therefore, to summarise this data to a page then do pivot will be fairly time consuming.

**arlu1201** · 06-05-2013, 02:47 PM

Not formula but a macro will make it easy for you. Would you be open to a macro solution?

**dreams1** · 06-05-2013, 03:01 PM

yes and thanks for macro solution

**arlu1201** · 06-06-2013, 05:49 AM

Do you have a sample file that you can upload? It will assist while working out the pivot for you.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**dreams1** · 06-11-2013, 04:53 PM

arlu1201,

Please find attached 'June 13 Hours'

sheet 1 to 5 represents number of days for each month. I add hours for each day on a separate sheet so by the end of the month, I will have 30 separate tabs/sheets.

I then need to summarise all this data into sheet 'staff', where I would prefer to have all the staff in all the sheets (mostly common for each day) in culumn A and then all the 'hours category' in row 3, showing cumulative hours for each staff and each hours category.

Thank you for your help, appreciated.