Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Refresh
If I Apply an auto-filter to the data-source of my Pivot Table
It does not effect to refreshed results of my PT. Can someone confirm this -- that is from a Pivot table data-sources records - any hidden rows (from an auto-filter) do not get omitted. The PT uses ALL record, Hidden or not.. TIA, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Refresh
Hi,
That is the expected behavior. If you hide the rows of the data souce using any technique, the pivot table ignores that, it uses all the data in the data area. 1. You can add a dummy column to the data source with formulas such as this =SUBTOTAL(1,C2) 2. In this case C2 is a numberical cell in your data. Copy the formula down. 3. Apply a filter to the data sourse just like you normally do. Although you can't see it the formulas on the hidden rows return #Div/0 4. Change the pivot table source range to include the new column, the one with the formula 5. Place the new field in the Page (Report) area 6. Display all the entries in the page area except the Div/0. -- If this helps, please click the Yes button Cheers, Shane Devenshire "JMay" wrote: If I Apply an auto-filter to the data-source of my Pivot Table It does not effect to refreshed results of my PT. Can someone confirm this -- that is from a Pivot table data-sources records - any hidden rows (from an auto-filter) do not get omitted. The PT uses ALL record, Hidden or not.. TIA, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
Pivot Table refresh | Excel Discussion (Misc queries) | |||
refresh a new worsheet on pivot table refresh | Excel Worksheet Functions | |||
Pivot Table Refresh | Excel Worksheet Functions | |||
Pivot table refresh | Excel Discussion (Misc queries) |