Pivot pull-down shows non-existing choices

[XL_sunshine]

Hi. I'm pulling data from Access into a pivot table. I refresh the data every month, changing the query in MS Query to span a 12 month period. That is, I go from January to December of last year, and the next month I change that to go from February of last year to January of this year. My problem is that when pressing the arrow that shows the pull down menu with the choices in one of the fields, I get choices that don't have data. For example, in the Region field I would get North America, EMEA, Asia, (blank) and Germany, when there are no records with (blank) or with Germany at this time. Maybe there were some records with those entries in the past, but those were mistakes that were fixed in the Access database; there are no records for the period of time I'm pulling that have those words in that field. I don't want them to show as choices. Is re-making the pivot table my only choice? It doesn't make any difference if I check or not the "show items with no data" check box for that field. Thanks.

 

[SydneyGeek]

Hi, and welcome to the Board!

As far as I know this is a known bug in Excel 2003 and before. Drives me nuts!
I remember reading on the Excel 2007 blog that this issue was meant to be fixed in 2007, but that doesn't help if you aren't using it...

Unfortunately the only workaround I've found is to re-create the PT.

Denis

 

[XL_sunshine]

Thanx

Thank-you, Denis, I was affraid that would be the case.

 

[Fazza]

This issue comes up on the forum repeatedly. Link describes solutions, as do numerous posts in the main forum. regards, Fazza http://www.contextures.com/xlPivot04.html

 

[XL_sunshine]

Thank-you so much, Fazza. This will really help. I'll add a button to my custom toolbar to do it.