Hi there,

Is it possible to show a pivot table with detail but have the data only show
for the subtotals?

I am trying to create a report that combines a staff list from HR to a
budget list from Finance. It needs to show staff in post vs Budget. But the
budget only applies to the department/Band combinations - not individuals. I
have been asked to include names of people in the report so managers can see
the breakdown of each person's WTE against the budget - here's an example of
what I am thinking it might look like:

Dept Band Position Name Staff In Post
Budgeted WTE Diff


Widgets A Widget Rep Joe Bloggs 1.00
Jane Doe .5
Total A 1.5
1.5 0
B Widget Mgr Homer Smith 1.00

Mary Brown 1.00

Total B 2.00
1.00 1.00
Total Widgets 3.50
2.5 1.00
Gadgets A Gadget Rep John Bloggs .67

Jenny Smith .8

Gadget Clerk Jimmy Choo 1.00

Total A 2.47
2.00 .47
etc...

by inserting a column into my staff list and creating a formula that looks
up the Dept/Band Budget and averages against the number of staff in each
budget section, I've managed to get as far as this:
Dept Band Position Name Budgeted WTE Staff In
Post Diff


Widgets A Widget Rep Joe Bloggs .75
1.00 -.25
Jane Doe .75
.5 .25
Total A 1.5
1.5 0
B Widget Mgr Homer Smith .5
1.00 .5
Mary Brown .5
1.00 .5
Total B 1.00
2.00 1.00
Total Widgets 2.5
3.5 1.00
Gadgets A Gadget Rep John Bloggs .67 .67
0
Jenny Smith .67
.8 .13
Gadget Clerk Jimmy Choo .67
1.00 .33
Total A 2.00
2.47 .47
etc.....

But having the budget averaged across each person shows differences to
budget where there may not even be any, and this creates confusion. I have a
feeling this might not be possible without taking this out of a pivot table
by doing a copy/paste values, and then manually making the changes, but with
having 50 of these reports, each one about 4 pages long, that sounds like a
nightmare. I'm open to other options for formatting the whole report as well!

Many thanks for all your help,

Lynn