Solved

Excel:  How do I remove redundant selections from a pivot table's page fields?

Posted on 2002-06-19
8
317 Views
Last Modified: 2008-03-10
I have a pivot table which is regularly refreshed from a large DBF datasource (which is accessed by opening it within EXCEL).  However when the pivot table is refreshed, some of the pivot table's page fields continue to show selections relating to the previous dataset.  When these are selected a blank pivot table is generated because such items are not represented in the new dataset.  Why are these out of date selections shown and more importantly how can they be removed?  Is VBA required?

Thanks in advance

Tam
0
Comment
Question by:Tamalaine
8 Comments
 

Expert Comment

by:marius8m
ID: 7116310
Are you using office97? Where was a bug on this things.
Are you sure having the automatic calculation activated? (If you push F9 something change?)
0
 

Author Comment

by:Tamalaine
ID: 7121257
Yep, using office / Excel 97!  Changing to office 2000 isn't really possible unless that's the only way to deal with the issue
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7328385
Hello Tamalaine

you could try it with VBA

-Hit ALT-F11 to bring up the VBA editor.
-Double-click on the appropriate worksheet on the tree in the upper-lefthand area of the screen, the area that shows all the sheets and any modules, etc.
-Paste in the following code. If you have named this pivot-table something other than "PivotTable1", then change the name in the .RefreshTable line.


Private Sub Worksheet_Calculate()
    ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub



HAGD:O)Bruintje
0
 

Author Comment

by:Tamalaine
ID: 7332510
I posted the question so long ago that I've lost the dataset I would have tested it on so I can't test it!

You're welcome the points anyway but would using VBA to refresh the data in the pivot table be any different from doing it manually (which didn't work)?

(NB I'll give you the points after you respond because I can't remember if you can post comments after accepting a comment as an answer)

From what I've learnt since posting the question, I suspect a) the pivot table object may have been corrupted and b) the solution probably involved purging the pivot cache object.

Regards Tam
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 44

Accepted Solution

by:
bruintje earned 100 total points
ID: 7333526
Hello Tam, the bit of VBA would do the same as refreshing by hand, but if it would work or not in your situation we'll never know :)

btw we can also aska mod ot move this to PAQ, and refund your points. and we can always post after accepting an answer

:O)Bruintje
0
 

Author Comment

by:Tamalaine
ID: 7334078
Thanks for your info re: using experts exchange but it's alright, have the points anyway!
Regards

Tam
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7334088
thanks Tam until the next time
0
 

Expert Comment

by:kjdubya
ID: 12988553
Pivot tables can indeed become corrupt, particularly the pagefield cache. The fix is simple.

1) Make sure that there are no other pivot tables directly beneath pivot table that needs fixing (If there are, you will have to temporarily move the broken pivot table to a location underneath the last pivot table on the sheet. You can move it back to its original location once it's fixed).

2) Then, remove the page field from the table - right click anywhere in the table and select "Show Field List" from the popup menu, and then click and drag the page field back to the field list.

3) Next, right click the table again and select "Refresh Data".

4) Finally, go to the Field List and drag the original pagefield back to the pagefield postion on the pivot table. That should do it!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now