Pivot Table “Muscle Memory” Problem

[Gian624]

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-comfficeffice" /><o></o>
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></o>
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></o>
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></o>
Thank you so much!!
Matt

 

[xenou]

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]

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-comfficeffice" /><o> </o>
Thanks again,
Matt

 

[xenou]

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]

Thanks, I'll try that tomorrow.

 

[Fazza]

Hi, Please refer http://www.contextures.com/xlPivot04.html HTH, F

 

[foverman]

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]

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-comfficeffice" /><o></o>
<o></o>
I know, it’s not my fault we are still using 2000.<o></o>
<o></o>
Thanks again,<o></o>
Matt<o></o>

 

[Fazza]

I don't know, Matt. Can only suggest you 'double check' that every step of the solution is followed correctly.

 

[xenou]

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