Dear,

 

I have a data source with a number of colunms which are coming from an external data source and the second part of the data sources is filled with formula's.

in my pivot table I use 2 fields for filtering, these fields are coming from calculated colums.

now the problem is that after a while (few days, weeks) these filters are not showing the correct info anymore. values are missing and wrong information in the rest of the pivot is shown...

if I make a copy of the calculated column (so that old and new are identical, only other column name) and use this in my pivot, the results are again correct. but after a while, I have the same problem again... I'm using Excel 2010

I can provide the excel file if needed.

 

wkr,

 

Filip

Hi,

Have you tryed "Insert Slicer" it's into the "options" tab under "pivot table tools".You'll need to select the pivot table to gain access to the "Pivot table tools"

 

Renatoabc

Also Tried that,

the missing data (from report filter) is also not showing up in the slider.

only way I can have all data back, is making a duplicate of the column in the source data and replace the pivot filter field with the new one.

very weird...

Hi five1234!

 

Does it happen just with one specific file?

Does it happens only in a specific machine?

 

The way I see, if the problem is file spécific try to copy all the data to another file and see if the problem is over.

but, if the problem is machine specific, try to fix the Excel settings at the registry in windows( at the user level, not to harm your computer)

For this, close all the Excel files that were oppened at the time, and:

1)Press the "Windows" and "R" keys and type REGEDIT and press open, to start the Registry editor in Windows

2) open the following folders (hives) in sequency:

HKEY_CURRENT_USER >> SOFTWARE >> MICROSOFT >> OFFICE >> 14.0

Inside 14.0 Right click Excel and rename it to Excel.old

3) Close the Registry Editor, open Excel and see if the problem is over.

Note: The folder 14.0 is for Excel 2010, 12.0 is for Excel 2007, and 11.0 is for Excel 2003

Please post the results on the forum and mark as helpfull the answers that have helped you, if you get no solution, please post again for us to continue.

Regards,

Renatoabc 

 

it is on all machines. we are all working with office 2010.

 

Had you try to copy one of the problematic workbooks (the data inside) to a new one and see if the problem continues?

Maybe it's the case of a corrupted file(s).

If in a network, what happens if you use the administrator account to access the Excel file?

Renatoabc

• Edited by renatoabc Tuesday, January 24, 2012 2:34 PM

if you want, I can send the file so you can see what the problem is.

wkr,

 

Filip

Hi Five1234!

Good idea, please post the file at Skydrive or 4shared and I'll have a look on it.

Post the link here on the Forum.

Don't forget to send the password if you set one.

 

Regards,

Rennatoabc

Hi Filip

As an alternative you can send it to renatoabc (at) hotmail (dot) com, I'll see what I can do.

Regards,

Renatoabc

you got mail ;)

Hi Filip,

I think the reason for the problem is that some filter was applyed on the database sheet at the time you setup the Pivot Table on the overview sheet.

Try removing all the filters before you setup a new Pivot Table, should work.

Any questions, please post again.

Renato

 

• Marked as answer by Rex ZhangModerator Monday, February 06, 2012 12:34 AM

Hi,

 

I am writing to see how everything is going with this issue. Is the problem resolved? If there is anything I can do for you, please feel free to let me know.

 

Best Regards, 

 

Rex Zhang

 

Forum Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

Hi,

 

As I have not heard from you for several days. I will go ahead and close this thread. If you have any questions, please feel free to reply to us and this thread will be re-opened.

 

Best Regards, 

 

Rex Zhang

Dear Rex,

Looks like I have the same problem.

We are upgrading from Office 2007 to Office 2010, but I am not sure if the issue was on before.

My source contains data that is not shown in the pivot. Some parts of the lines stay blank.

The file has 54000 rows, but I cannot imagine this is problematic.

Above comments are carefully read, but so far no solution.

Pls also observe the last line... for UCS 97042011... where additonally the 2 records are combined in 1 line

Any suggestions is really welcome.

Cheers,

T

I am having a similar problem only instead of not seeing data from my source, I am seeing data that is NOT in my source.  Only seems to be effecting one of the fields.

Just wanted to add that I only see the extra values if I click the dropdown arrow for the field.  It doesn't pull in those weird values to the table.  But, if I try to access the pivot items to copy the values to another place in the workbook with vb, it brings in those extra, bad values that really don't exist in my source.  BTW, source is a table that is refresed from an external source.

I am also having the same problem. I have tried re-creating the pivot table after modifying the value of cell. I have tried unfiltering all rows in my source data. I have tried removing filters entirely from my source data. One particular row still thinks it has "Blank" for one of my columns when it in fact has a real value. 

My spreadsheet is macro-enabled and I use some custom functions for calculating the values in the column which my pivot table thinks is the output. Note that the column that is showing the erroneous blank does not have any custom functions or formulas at all. The problem column is only a text entry. 

Excel 2010, 32bit on Win 7 Enterprise x64

• Proposed as answer by psjo Thursday, September 25, 2014 7:53 AM
• Unproposed as answer by psjo Thursday, September 25, 2014 7:53 AM

Hi,

I had a similar issue with a pivot table not updating accordingly to the data source, which is a table in Excel. When converting the table to a regular range of data (http://office.microsoft.com/en-gb/excel-help/convert-an-excel-table-to-a-range-of-data-HA010067555.aspx), that solved it for me. Now the pivot table updates according to changes in the data source.

Regards,

/Per

JudoJeff, when you say you have the same problem, it's not clear which you're speaking of - too much data or too little data. 

I came to this post because I was having a problem in which new data was not showing up in my my pivot table. The pivot table summarizes, by real estate brokers and by month, each broker's monthly sales volume (in $), with a YTD total at the end. 

My source data is one sheet of a workbook. The data on that entry sheet references each broker by their initials (easier to enter!). Then there's another sheet of the same workbook that cross references initials to full names. I have a lookup function in a column of my source data sheet, and it's necessary to make sure I pull that formula down from the previous complete line to make sure I'm doing the broker name lookup in my source data before I go to my pivot table. The pivot table, an end-user report, shows by broker name, not initials. If I forget (as I had in this case) to pull the lookup function into the newly added rows, my pivot table will not update the information for those brokers. 

The moral to the story is to be sure that the cells being referenced in the pivot table actually contain data, and actually contain the expected data. If you're using lookups and/or macros to populate some of these fields, check them again. 

Get some sleep, and try it again. :)

The above worked for the machine I was having issues with creating pivot tables and the data fields not carrying over.