Making a Master Consolidation Worksheet

S

shahid5788

Board Regular
Joined
May 24, 2016
Messages
91
Hi,

I was wondering if there a way to make a master consolidation master sheet in excel. I currently with large volume of worksheets. And then I have to manually copy paste the workbooks one by one. I currently receive about 80 excel worksheets and than manually have to consolidate them. I am hoping to find a better and effective way in order to consolidate these.

In these worksheet are are set of projects and the team records on of how many hours they worked on a corresponding project based on the day of the month.


I need to extract the total hours of each project and also the total of employee hours worked on that particular month.

As you can see example of worksheet below. I get about 80 other worksheets in which I have to manually extract the data of each employee and the project total and monthly totals. I am trying to find a more efficient other than just v-lookups. Hoping to find a way in which there is a way to consolidate where it would spit out the data I need (I.E ex "Employee Name" "Project A Total" "Monthly Hr Totals"



[TABLE="width: 1218"]
<colgroup><col><col><col><col span="26"><col></colgroup><tbody>[TR]
[TD]James[/TD]
[TD] [/TD]
[TD]Jan[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Jan[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Jan[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Jan[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Su[/TD]
[TD]Mo [/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[TD]Mo [/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[TD]Mo [/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[TD]Sa[/TD]
[TD]Su[/TD]
[TD]Mo [/TD]
[TD]Tu[/TD]
[TD]We[/TD]
[TD]Th[/TD]
[TD]Fr[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Project Code[/TD]
[TD]Project Title[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]C[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD]108.0[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]E[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]F[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Daily Totals[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.0[/TD]
[TD]108.0[/TD]
[/TR]
</tbody>[/TABLE]
 
You must log in or register to reply here.

Similar threads

L
  • Question Question
Consolidate calendar inputs into a master sheet
Replies
1
Views
578
Linki
L
J
  • Question Question
better way than consolidating?
Replies
1
Views
530
severynm
severynm
F
  • Question Question
Excel VBA code update to consolidate multiple sheets in master sheet and edit from master sheet
Replies
6
Views
4K
dmt32
D
J
  • Locked
  • Question Question
Still need help on a daily summary sheet
Replies
1
Views
641
Fluff
Fluff
H
  • Question Question
Losing my mind over this. Is my assignment incorrect or do I just not know what I'm doing? (regarding utility invoices & calendarizing data)
2
Replies
12
Views
1K
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top