pivot table define layout and totals differently per value field?

[SvenBlomme]

Hello,I am working for a healthcare service provider. For keeping track of our actual time spent versus budgeted time, I would like to create a pivot table which contains this information. In this table, I would like to display the actual time spent on our tasks, in columns per month, with the grand total at the end of each row (in the far right of the table). However, I would like to display the budgeted time next to these actual times, but ONLY the totals, not the breakdown per month. Is it at all feasible to do this, either with or without VBA? I tried to display below what I would like my table to look like. Assume that for a certain task (service1 in below example) , 50 hours are budgeted. In month 1: 10 hour spent. In Month 2: 5 hours spent. In month 3: 20 hours spent. I would like my pivot table to display the actual hours spent per month, followed by a column which displays the total of the actual hours spent, followed by a column displaying the budgeted hours for this task (and then another column to discplay the difference between both - I kno whow to do this part though). I hope the image below clarifies this description.

TASK Month 1 Month 2 Month 3 Total months Budgeted Difference
service1 10 5 20 35 50 15

So basically my question is: can I have 2 value fields in one pivot table, where one of the value fields is distributed over different columns and also displayed as total, whereas the other value field only displays the total (not distributed over diffrerent columns)?

Any suggestion would be most welcome!

Thanks a lot,
sven

 

[SvenBlomme]

theactueal time spent is taken from our time registration tool. This contains how many hours were spent on which task, and by whom, and the date on which the hour were spent. I use this date create extra columns (one which calculates the month based on the date, and one which does the same for the year).

 

[Andrew Poulsom]

Please post a sample of the data that you are actually using for your pivot table, stating which fields it contains and where.

 

[SvenBlomme]

Hi, the tool I am creating contains confidential data so I will send it to you in a private message.

 

[Andrew Poulsom]

Please create a sanitised sample of just the source data and post it.