Solved

Strange characters in Excel Pivot Table drop-down box

Posted on 2012-03-30
5
561 Views
Last Modified: 2012-04-10
My Excel pivot table is linked to an Access database.  The Access database was corrupted and had some strange characters in the records.  The strange characters appeared in the drop-down box when filtering the pivot table.

I have cleaned up the Access database; however, the strange characters still appear in the pivot table.  Do any experts know how to remove them?  Besides cosmetic, they are a nuisance when selecting various records.

Attached is a screen-shot of the filter drop-down box.

Thank you in advance for your help.
Doc4.doc
0
Comment
Question by:jack3_99
  • 3
5 Comments
 
LVL 20

Expert Comment

by:clarkscott
ID: 37787585
Try REFRESHING the Pivot tables.

Scott C
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37787805
My guess is that Scott's suggestion will resolve your issue

"Corrupted" Access data can cause all sorts of issues, especially when spanning different applications.

So if that does not work, simply link the entire table into Excel and see if you can identify the "odd" entries in both Excel and in Access.

A more aggressive "cleanup/repair" may be needed.
(Compact & Repair, Compile the code, JetComp.exe, Create a new db and import all the objects, ...etc)
Corrupted Access data will not only "Jumble" some data, but it can also delete or add data, or even records...

;-)

JeffCoachman
0
 

Author Comment

by:jack3_99
ID: 37788915
I had already tried refresh; however, the odd entries remained.  

I believe the problem is with Excel.  I deleted the corrupted Access database and used a backup copy (before the corruption); so, this database was never damaged.  

I am hoping that I don't need to set up new pivot tables as I have 20+ in the workbook.

I appreciate any help.  
Thank you.
0
 

Accepted Solution

by:
jack3_99 earned 0 total points
ID: 37813050
I found the following at another site that was able to fix the problem:

To prevent old items from being retained in an Excel 2007/2010 pivot table, you can change an option setting:
- Right-click a cell in the pivot table
- Click on PivotTable options
- Click on the Data tab
- In the Retain Items section, select None from the drop down list.
- Click OK, then refresh the pivot table.
0
 

Author Closing Comment

by:jack3_99
ID: 37826781
This allowed the clearing of the old corrupt items.  I am not sure the reason the Pivot Tables keep the information.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

820 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