PDA

View Full Version : Solved: Pivot Table problem



Sir Babydum GBE
04-19-2006, 06:08 AM
Hello,

Long time no hear – please forgive my absence!

I need some help...

Attached is a workbook that I want to modify. Once opened – press CTRL+Shift+S to reveal reports tabs.

Background:
We have to keep a record of expenses for individuals who work on our department. People can work on different projects, so our reports need to show expenses either by person or by project.
To do this, there is an input sheet on which staff enter their expenses according to category. On this sheet there is a dynamic named range, MyPivotArea, which expands to match the size of the current data area and upon which some Pivot Tables are based.

In all of the categories bar one the amount input by the user is a monetary one. But in just one the figures entered represent hours. Therefore on each report sheet there are two pivot tables – one exclusively for the hours – so that this does not artificially inflate my summed monetary amount.

Problem
In order to hide items in the pivot table wizard – I had to create fake entries in the input sheet (so that they appeared in the wizard). But now my pivot table reports show loads of redundant data. I’ve cleared the fake entries, but I can’t auto-hide names or projects. So there’s an entry on the report for every single person or project – even though (at the moment) only 4 people have entries on my input sheet.

There is code in each sheet to refresh the tables, but how can I get the tables to show only data that is useful – and not pages full of blank entries?

A long one – I know. Sorry about that!

acw
04-19-2006, 11:11 PM
Hi

Try selecting the pivot table (I right clicked on the name), then Field Settings, then deselect the Show Items with no data.

Tony

Sir Babydum GBE
04-20-2006, 12:24 AM
Hi

Try selecting the pivot table (I right clicked on the name), then Field Settings, then deselect the Show Items with no data.

TonyWhat a flipping idiot! (me, not you).

Thanks Tony.

geekgirlau
04-21-2006, 04:51 AM
Hi Babydum - I've marked this thread as solved for you (thread tools still not working)

Sir Babydum GBE
04-21-2006, 05:50 AM
Hi Babydum - I've marked this thread as solved for you (thread tools still not working)Thanks