Pivot Tables: Don't show data for detail, but still show subtotals

[Lynndyhop]

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

 

[Lynndyhop]

Just realised my example table doesn't format well to the window and looks a
bit nightmare-ish...here it is again - I hope this gets the idea across...

Dept Band Post Name WTE Budget Diff

Widgets A Rep Joe Bloggs 1.00
Jane Doe .5
Total A 1.5 1.5 0
B Mgr Dan 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 Rep John Blue .67
Jenny Smith .8

Clerk Jim Choo 1.00
Total A 2.47 2.00 .47


What I've currently got:

Dept Band Post Name WTE Budget Diff

Widgets A Rep Joe Bloggs 1.00 .75 .25
Jane Doe .5 .75 -.25
Total A 1.5 1.5 0
B Mgr Dan Smith 1.00 .5 .5
Mary Brown 1.00 .5 .5
Total B 2.00 1.00 1.00
Total Widgets 3.50 2.5 1.00

Gadgets A Rep John Blue .67 .67 0
Jenny Smith .8 .67 .133
Clerk Jim Choo 1.00 .67 .333
Total A 2.47 2.00 .47

Many thanks for your help - I *really* appreciate any feedback I can get! :0)

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Distribute budget.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_04_10.xlsx

 

[Lynndyhop]

Thanks Herbert,

I'm using Excel 2003, so not sure if its not converting properly - it looks
like you've got it to the same place I do, but I need the Budget and
Difference to disappear at the employee level so it just shows the totals for
those columns. The only column that should have the detail is the 'WTE'
column.

I figured out a bit of a cheating way to do it - I've formatted the column
to show text in white so you can't see it, then use the 'select'/enable
selection to select the totals only and format those with black text so they
show up. But it messes up being able to do 'Traffic Light Formatting' on the
'Diff' column.

Many thanks for your help,

Lynn