Merging/ Adding values from same cell in different excel sheets

[shikarishambu]

I have a template - a timesheet template with projects as rows and dates as columns and a sheet in the workbook for each month.

Each developer fills a timesheet and sends it. Timesheet_D1, Timesheet_D2, Timesheet_D3
I want to build a consolidated timesheet that consolidates the values for each month.

Is there a way to do this without having to resort to programming?

Thanks

 

[r2c2]

I have a template - a timesheet template with projects as rows and dates as columns and a sheet in the workbook for each month.

Each developer fills a timesheet and sends it. Timesheet_D1, Timesheet_D2, Timesheet_D3
I want to build a consolidated timesheet that consolidates the values for each month.

Is there a way to do this without having to resort to programming?

Thanks

Oh yes, simply use either Power Query or consolidated pivot tables. If you can post a sample file I can show you how to do this with PQ.

 

[shikarishambu]

Oh yes, simply use either Power Query or consolidated pivot tables. If you can post a sample file I can show you how to do this with PQ.

Here is the file. This file is provided to each member of the team. I want to have a consolidated version that shows in the summary sheet the totals by month and in the individual sheets the totals by day. Thank you for your help

 

[GraH - Guido]

Hi,
What @r2c2 meant is that we need some raw data sample. Then making this kind of total sheets per MonthDay/YearMonth total is like a walk in the park with Power Query.
Would you have this feature (it is called Get&Transform is later versions) in you Excel version?

 

[shikarishambu]

Hi,
What @r2c2 meant is that we need some raw data sample. Then making this kind of total sheets per MonthDay/YearMonth total is like a walk in the park with Power Query.
Would you have this feature (it is called Get&Transform is later versions) in you Excel version?

I do have excel 2016. I posted a sample file with data in the Jun sheet. You could use multiple copies of the same as sample timesheets for each person.

 

[bosco_yip]

Here is a formula solution,

In C2, copied across and down :

=OFFSET(INDIRECT("'"&C$1&"'!$A$1"),ROWS($1:1),DAY(EOMONTH(--(1&"/"&C$1),0))+2)

Regards
Bosco