Solved

Strange characters in Excel Pivot Table drop-down box

Posted on 2012-03-30
5
582 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

717 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