SUM the split of working hours to another spreadsheet??

I have 2 spreadsheets where one provides me with total hours for each member of staff while the other gives me the split of the same hours i.e. Ash worked 15 hours while the other confirms 6 hrs regular, 2 hours standard, 4 hours overtime, 2.5 hours travel, 0.5 hours unpaid. The first sheet provides a monthly total and the 2nd sheet gives a daily breakup (example attached - Sheet A).

I want to be able to SUM all the daily hours for each person using multiple criteria - year, month, employee number to bring back each of the types of hours (Regular, Standard, Overtime, Travel, and Unpaid). I cannot use the persons name as we have multiple staff who have the same name so I have been using their unique employee number instead.

I have put the sample data through a pivot table to get the monthly totals in the test sheet but need to pull this through to a new workbook (lets just call it Sheet B).

What is the best solution? Should I use the pivot table data and then pull the date over to a new spreadsheet using GETPIVOTDATA? If so what is the best way to do so as it needs to be handle a large number of staff members and multiple criteria so needs to be dynamic? People I have spoken to have suggested using SUMIF and reference the original data and not use the PIVOT at all but I am planning on doing dynamic pivot charting and the pivot table option does a lot of the calculations better and leave less room for errors.

I have attached the sample sheet.

Thanks you in advance.

In E3, copied across and down??

=SUMIFS(Daily!$I:$I,Daily!$A:$A,Monthly!$B3,Daily!$B:$B,Monthly!$A3,Daily!$E:$E,Monthly!$D3,Daily!$K:$K,Monthly!E$2)

Two key observations:

1. You should never have a dataset that relies on multiple fields to identify an individual: no matter how hard you try, this can (and eventually will) lead to duplication and then misidentification of an individual. Add a table to your dataset with a list of individuals and give them a unique ID, then apply this ID to any 'transaction involving that individual.

2. If you downloaded and installed the PowerQuery add-in, you could bring the data from the source file into a new file in the format you want.

You will then need a) to stop using a structured table and b) use SUMPRODUCT.

See file. Download both to same location. You may need to change the path. It may be OK to use Named Ranges to pick up the source data in a dynamic way. I never use Pivot Tables any more - Remembering to refresh them is too complicated for my wee brain.

LoL. I was once the "King of the Pivot Table" in work, but drifted away from using them in favour of dynamic formulae. Incidentally, don't use whole column ranges with SP. But you could also make them more future-proof by extending the range to 1000... or other sensible end point.