Pivot tables, sorting data into a useable way to use formulas against

Hi All,

I’m trying to do a reconciliation but I have something causing an error with my totals when I perform some lookups. The purpose of the rec is to compare the costs are timesheet system is producing per agency employee (using staff ID numbers), looking at the total costs for each of the individual week and then compare them against invoices we approved for each person.

The attached demo spreadsheet has just 3 of the tabs I’m using:
1. “source – timesheet costs”: This has a line for every week ending date the employee worked and the total cost for that week. There may also be multiple lines for that particular employee and W/E date if an adjustment was made to that week
2. “Source –GL 61310” this would be where all the invoices approved will be logged, containing data with columns for the staff ID number, W/E date, invoice number and invoice net total.
3. “Timesheets compared with GL61310”: Here I copy/pasted the timesheet data and use sumifs formulas to compare the totals of invoices posted against matching staff ID numbers + matching W/E date. This will give me the difference between the costs the timesheets say we should pay out to the costs we have paid out on invoices.

Then what I would ideally do next is paste the “source – timesheet costs” tab data into tab “Timesheets compared with GL61310”. Then column ‘I’ will use a SUMSIF formula looking at the total cost of invoices received for a match on staff ID and W/E dates. Then Column ‘J’ will compare G and I which are timesheet costs against invoice costs.

The problem with this is since there can be multiple lines for a staff ID for a particular W/E date because of adjustments, when I total columns I and J I am not getting the correct totals. Column I is showing a total of £4,473.64 worth on invoices approved, but on tab 2 where the invoices are logged there are only £3,131.54 worth of invoices. Ideally I need to get rid of all multiple lines from the timesheet data, so just have one line per week per person.

My first thought was a Pivot Table which does the job. But when I paste that into the other tab “Timesheets compared with GL61310” where I am going to make the comparisons I need to have it in the format of individual columns containing:
1. Staff ID number
2. W/E date
3. Total salary costs

The pivot table does total up the data right but doesn’t have the staff ID number in a separate column on the left of the W/E date, followed by salary. It instead puts the data in blocks of staff ID, then all the weeks below it, then the salary costs in a column beside it on the right. With this I can’t use my SUMIFS formula to compare the timesheet costs with the invoices approved without having the data in the pivot table in a horizontal line in separate columns.

Any suggestion on how to get the pivot table to have a whole column populated vertically against each week? Or maybe another solution?

Cheers,

Shane