Update to Source data not updating in Pivot Table

[LukeN]

Windows 7, Excel 2010

I have inserted data on a single worksheet that populates around 10 pivot tables, each on their own worksheet.

One of the data fields contains names. One of the members of my team left and was replaced by a different person and so rows that contained that person's name needed to be updated. I opened the sheet with the original data and replaced the name of the team member that left with the new team member.

When I tried to update the pivot table, it did not update. What I mean by this, is that I refreshed the pivot table using the refresh button in the ribbon. I then went to the relevant pivot table field in the field list, "Project Owner" and clicked the down arrow. The check boxes that allow you to filter the data still offered the name of the person that left and who's name had been eliminated from the data.

I also tried to do the same thing by pulling the "Project Owner" field from the field list into the report filter window subsequently attempting filter in the pivot table. I achieved the same result.

Is there a way to get rid of the name of the person that had been eliminated from the original data so that I only see current data listed in the field filter?

 

[LukeN]

Bumping back up. Thanks all!

 

[aimwhy]

I'm having a similar probelm. I found if I go to the layout and remove the field and add it back, SOMETIMES that will fix it. Hoping to find a quicker, better way!

 

[Philip_Trick]

It sounds like what you need to do is eliminate the retention of any deleted items.

Right-click on your pivot table, select Pivot Table options, and go to the Data tab.

Under "Retain items deleted from the data source" you'll likely see "Automatic" selected. Change this value to "None", refresh your table, and any previously deleted fields will now be permanently removed from your pivot table filter.

 

[aimwhy]

What if I'm using Excel 2003?

 

[aimwhy]

In case anyone else is looking, I found help for several versions here:
http://www.contextures.com/xlPivot04.html


This is the macro recomended for 2003 and it helped me.
thanks

Sub DeleteMissingItems2002All()'prevents unused items in non-OLAP PivotTables'pivot table tutorial by contextures.comDim pt As PivotTableDim ws As WorksheetDim pc As PivotCache'change the settingsFor Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Next ptNext ws'refresh all the pivot cachesFor Each pc In ActiveWorkbook.PivotCaches On Error Resume Next pc.RefreshNext pcEnd Sub[FONT=Arial, Helvetica, sans-serif][/FONT]</PRE>

 

[Coryj5]

It sounds like what you need to do is eliminate the retention of any deleted items.

Right-click on your pivot table, select Pivot Table options, and go to the Data tab.

Under "Retain items deleted from the data source" you'll likely see "Automatic" selected. Change this value to "None", refresh your table, and any previously deleted fields will now be permanently removed from your pivot table filter.

This worked perfectly and saved me hours! Thanks!

Question: What would be the reason for keeping deleted data? Seem the data would not be there and only the titles remained, which would have no use.

Thanks,
Cory