Calculating total effort per employee

[Abhi4joy]

Hello All,

I have master spread sheet where the data is coming from different worksheets.

Basically have different employees working on different project(each project is on different sheet) with effort entered against each project.

And on master spread sheet i want to show all the project an employee associated with and effort spent . And also have Total effort(indicating the employee occupancy) shown.

I tried adding row directly in master spreadsheet but it disappears once employee is associated with new project.

I have used the below script to create master table

Please Login or Register  to view this content.

Attached is the spreadsheet


Thanks in advance
Abhi

[Logit]

.
The workbook does not open here. There is an error.

[Abhi4joy]

Have uploaded again.
Hope this work


cheers
Abhi

[alansidman]

Taking your table that you created in PQ, I converted it to a range (right click anywhere in the table and select Table-->Convert to Range). Once I had a range, I then went to Sub- Totals on the Data Tab of the Ribbon and set the sub totals to every change of Employee ID.

Data Range

	A	B	C	D
1	EmployeeId	EmployeeName	Project	Effort in hours
2	1235	Aldo	P3	0.2
3	1235	Aldo	P2	0.2
4	1235 Total			0.4
5	1237	Dan	P3	0.3
6	1237	Dan	P2	0.3
7	1237 Total			0.6
8	1234	Don	P3	0.1
9	1234	Don	P2	0.1
10	1234 Total			0.2
11	1236	Rob	P3	0.4
12	1236	Rob	P2	0.2
13	1236 Total			0.6
14	Grand Total			1.8

You could also take your PQ result and build a Pivot Table either within PQ or within Excel. Here is what a PT would look like and you would not have to convert the table to a range.

Data Range

	F	G	H	I
15	EmployeeId	EmployeeName	Project	Sum of Effort in hours
16	1234
17		Don
18			P2	0.1
19			P3	0.1
20		Don Total		0.2
21	1235
22		Aldo
23			P2	0.2
24			P3	0.2
25		Aldo Total		0.4
26	1236
27		Rob
28			P2	0.2
29			P3	0.4
30		Rob Total		0.6
31	1237
32		Dan
33			P2	0.3
34			P3	0.3
35		Dan Total		0.6

[Abhi4joy]

Thanks it did work. Used the pivot table .
However for some reason my master sheet is not automatically updation when new row/columns added in other work sheet?
Do you see any issue with my script am using ?

[alansidman]

Are you clicking on Refresh twice?

[Abhi4joy]

Yes i did refresh twice
Basically i want to create master sheet based on other worksheets.

Want master sheet to update automatically when there any changes in the other worksheets..

Any guidelines/pointers would be much appreciated

Thanks
Abhi