Update to Source data not updating in Pivot Table

LukeN

New Member
Joined
Aug 9, 2011
Messages
2
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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!
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,609
Messages
6,179,874
Members
452,949
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top