Clearing out Pivot Table Dropdowns

[James_B]

Is there any way of refreshing the dropdown options on a pivot table? It seems that, when an underlying dataset changes, Excel (03) still leaves the 'old' options as selectable. So, suppose my date column is {'94 Q1', '94 Q2', etc.} and I change it to {'1994_Q1', '1994_Q2', etc.}, do I have to rebuild the table to flush out the old options?

 

[rallcorn]

On PivotTable Options choose DATA, Retain items deleted from the data source, Number of items to retain per field - set this to NONE

 

[James_B]

Magic! Thanks a bunch.

 

[James_B]

Having said that, I can't find the fields you're talking about! I'm using 03.

 

[rallcorn]

It's been several years, so I'm not remembering 2003 exactly, but I think if you right-click in pivot table you'll see pivot table options or at least get you to the PT Wizard & if you go "back" enough times you'll get to something where you can set the PT Options.

 

[James_B]

Yeah, I can get to the Options page alright. I just don't see the fields you're referring to.

 

[rallcorn]

OK - I pulled out an old PT book. It says you must drag the date field off of the current PivotTable, refresh the PT then drag the date field back into the PT.

Hope that works for you!

 

[James_B]

I'll give it a go tomorrow. Thanks for the tip!

 

[Fazza]

well document at http://www.contextures.com/xlpivot04.html