Pivot Table Data

[markjaxn]

I have a file that contains pivot tables along with some buttons and
macros to change the way the pivot table looks depending on what type of
information that you would like to view. The pivot table is based on data
on a separate worksheet but is within the same workbook (separate tabs).
If I change my data source (i.e. I change the names of individuals to new
names) and refresh my pivot table, the information changes like I want it
to. However, if I decide to filter or hide a name in the pivot table by
clicking on the drop-down diamond, it has a list of names that are no
longer in the data source.

My question is this: Is there a way to remove the names of people that
the pivot table thinks is still part of the data but is not really there
(the names don't show up in the pivot table output, only in the drop down
menu when trying to filter individuals)? The only thing I can think of is
to create a new pivot table but then I need to re-format everything and
re-program my buttons and macros. Thanks in advance.

 

[Andy Brown]

See Debra's instructions for this @

http://www.contextures.com/xlPivot04.html

Rgds,
Andy

 

[Robert Rosenberg]

In your case, since you don't want to disturb your PT, you need to use a macro:

Sub UpdateDropDownLists()

Dim oPT As PivotTable
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
For Each oPT In wks.PivotTables
oPT.PivotCache.MissingItemsLimit = xlMissingItemsNone
oPT.RefreshTable
Next oPT
Next wks

End Sub

This one loops through every PivotTable on every worksheet in the active workbook and sets the dropdown lists to supress the display of items that are missing in the PT's raw data.
_________________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
http://www.r-cor.com



I have a file that contains pivot tables along with some buttons and macros to change the way the pivot table looks depending on what type of information that you would like to view. The pivot table is based on data on a separate worksheet but is within the same workbook (separate tabs). If I change my data source (i.e. I change the names of individuals to new names) and refresh my pivot table, the information changes like I want it to. However, if I decide to filter or hide a name in the pivot table by clicking on the drop-down diamond, it has a list of names that are no longer in the data source.

My question is this: Is there a way to remove the names of people that the pivot table thinks is still part of the data but is not really there (the names don't show up in the pivot table output, only in the drop down menu when trying to filter individuals)? The only thing I can think of is to create a new pivot table but then I need to re-format everything and re-program my buttons and macros. Thanks in advance.

 

[markjaxn]

Thanks for the link Andy. I figured there had to be a way. You have
helped me a bunch.

Mark

 

[Andy Brown]

You have helped me a bunch.

I just posted the link -- Debra did all the work.

Rgds,
Andy