Pivot Table “Muscle Memory” Problem

Gian624

Board Regular
Joined
Jul 23, 2009
Messages
91
I am somewhat of a novice with Pivot Tables. One problem I am having that no one seems able to help me out with is that when I update and refresh my pivot table, it seems like it has a muscle memory with the drop down choice boxes.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
For example:
Lets say I have three customers that I have labeled 01 Target, 02 Walmart and 03 Bed Bath and Beyond. If I want to change 01 Target to 02 Target and 02 Walmart to 01 Walmart, it works in the pivot table and shows the proper data but if I try to choose the customer from a drop down box, it remembers the customers as labeled as they were before I made the changes AND the current look.
<o:p></o:p>
I will see: 01 Target, 02 Target, 01 Walmart and 02 Walmart in the drop down box but the data will only show up in the pivot table associated with the proper customers as they are labeled in the data source.
<o:p></o:p>
Does anyone know how to make it so the drop down choice boxes only show what the data source says and how to clear out the “muscle memory” so I don’t have to rebuild the pivot table every time I make an adjustment?
<o:p></o:p>
Thank you so much!!
Matt
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
Are you making these changes in the pivot table? Basically, pivot tables are fed by the source data - so anything you change in the table (if its allowed at all) will be "cosmetic". You want to work with the underlying data, or the source data.

Alex.
 

Gian624

Board Regular
Joined
Jul 23, 2009
Messages
91
Hi Alexander,
Thank you for responding. I am making the changes to the source data. Basically, If I have hundreds of rows with the same customer in the source data and I change all of those customers to be a different customer (spelling change) when I go back to the pivot table, the drop down boxes will “cosmetically” show the misspelled customer and the corrected customer even though the source data has been changed to have the correct spelling. It might be cosmetic like you said and not actually feed any data if chosen but it still creates confusion with my superiors who are receiving the reports I am doing. I always need to go back and redo the pivot table so they are not confused. Any ideas how to get rid of the “cosmetic” problem?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Thanks again,
Matt
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
Try taking all data out of the pivot table and refreshing the "empty" pivot table. Then put the fields, etc. back in.

PT's "remember" missing items (there is a good reason for this which I've forgotten).

Alex
 

Gian624

Board Regular
Joined
Jul 23, 2009
Messages
91
Thanks, I'll try that tomorrow.
 

foverman

Board Regular
Joined
Sep 2, 2008
Messages
139
Gian624,

I asked more-or-less this same question on Friday:
http://www.mrexcel.com/forum/showthread.php?t=405161

The answer matches what was in the
Programmatically Clear Old Items -- Excel 2002 or later
section of the link Fazza posted. Worked like a charm. And it seems to keep working, because I put old data (2006-Q1) back in, refreshed the table, deleted it and refreshed again. The "ghost" data didn't show up.
 

Gian624

Board Regular
Joined
Jul 23, 2009
Messages
91
Thank you Foverman and Fazza. I tried your suggestions but it did not work for my computer. I had a co-worker who is much better than I am at excel and she could not get my pivot to work but was able to get it to work on something she threw together fast to test. Any idea why it would not work on my pivot short of actually seeing the pivot. I believe we are using excel 2000 if that helps. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I know, it’s not my fault we are still using 2000.<o:p></o:p>
<o:p></o:p>
Thanks again,<o:p></o:p>
Matt<o:p></o:p>
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
I don't know, Matt. Can only suggest you 'double check' that every step of the solution is followed correctly.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
I will say THIS POST is uncannily similar.

Gian, if your information is not sensitive and you'd like send a file to me with this strange behavior I'll see if "laboratory analysis" uncovers anything. I tend to agree with Fazza that this sounds like the missing item list but who knows - could be a new twist in Excel 2007 for us.

Alex
 

Forum statistics

Threads
1,082,151
Messages
5,363,437
Members
400,737
Latest member
vipamuk

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top