Solved

Strange characters in Excel Pivot Table drop-down box

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA Help 18 44
VLOOKUP 6 17
vba delete dups is not working 35 15
What is the best way to re-number row value from 1 to visible rows only in excel 6 17
My experience with Windows 10 over a one year period and suggestions for smooth operation
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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