My source data is consists of three columns - A: Company name B: Vehicle registration number and C: Tax expiry. The contents of column C are either Yes or blank.
My pivot table summarises the data, using the COUNT field setting to show (by Company) the number of vehicles with expired tax. The idea is to be able to double-click the number to produce a report for that Company.
The pivot table shows the correct data (e.g. 80 for Company X - which has 80 expired tax vehicles of a total of 700 it owns). However, when I double-click 80 to 'Show Details', the report shows all 700 records for that Company, not just those 80 with expired tax.
Hopefully this makes sense, but I don't understand why all 700 records are returned.
Please help!
Given the nature of the data (Yes or Blank) the drill through will detail all records as all records form part of the Count value.
I'd suggest moving Tax to a Report Filter / Page Field - set to "Yes" and use Count of Reg or Company as the Data Field.
When you drill into the resulting Count value only the "Yes" records will be returned.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Perfect! Thank you for such a quick response.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks