Need help to gather staffs times report
Scenario:
I've got 20 employees who everyday register their working hours into certain categories. As you can see in the file attached every staff member has got 53 sheets in their workbook. One for every week in the year and a finally a total sum of the weeks.
Challenge:
What I need is a total of totals. Meaning i need to find way in which i can gather every staff-total into a new workbook where every staff members working hours are summed. It needs to be dynamic so that staff member a can type in his working hours and immediately after 'the total of the totals' are updated.
Woud you please give me a piece of advice in how this can be done in the most appropriate way?
Thank you!
- Martin
| Attachment | Size |
|---|---|
| Dørken, Martin 2012-2013.xlsx | 381.65 KB |
One or two questions and comments Martin
1. I see all the worksheets are in print preview mode is this to facilitate printouts for the employees ?
2. Is the real time updating absolutely necessary ?
3. Do you have Access or SQL because this looks more like ODBC type database problem given the criteria.
1. Yes 2. No what i want is a
1. Yes
2. No what i want is a macro that can be runned when i want it to be runned. The macro must gather all the i-columns on the staff-members totalsheet into a new total of totals workbook.
Could you help me with the code?
Need help to gather staffs times report
If you want such format for entry and totals, I recommend using 3-D formula.
1. Create new file (Totals for all employees) with the same structure.
2. Create SUM formulas like you have already had, but refer to total sheets from each employee's file: to create a SUM for all employees, insert SUM and select totals from total sheet of each employees.
In this way, when you change data, totals for each employee will update, as well as total of totals will also be updated.
I would also recommend you to first re-create sums in Total sheets of each employee in a 3-D way described above.
If you need additional help, let me know.
Thank you for your
Thank you for your response.
What i want is a macro that can be runned when i want it to be runned. The macro must gather all the i-columns on the staff-members totalsheet into a new total of totals workbook.
Could you help me with the code?
I don't think you need code
Create new file with the same structure and 3-D formulas. It will have real-time data, no need for macro. If you need to distribute report further, make a copy and break links, that's all.
I'm sure we can Martin but we
I'm sure we can Martin but we need to plan the best way through it so we can avoid the pitfalls.
So what you need is a master workbook with the equivalent display of a single employee work week sheet that gathers all data for all employees on demand ?
Are all the employee time sheets stored in the same folder ?
Are all the employee time
Are all the employee time sheets stored in the same folder ?
Yes
So what you need is a master workbook with the equivalent display of a single employee work week sheet that gathers all data for all employees on demand ?
Yes exactly I need a master workbook which summes across the sheet named 'TOTAL' on every employees workbook.
Martin, check the response
Martin, check the response thread - I've posted a file for you to have a look at