Solved

Strange characters in Excel Pivot Table drop-down box

Posted on 2012-03-30
5
542 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Outlook Free & Paid Tools
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

910 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

21 Experts available now in Live!

Get 1:1 Help Now