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!
Reply With Quote
Originally Posted by acw
