disagreeing pivot tables

[miconian]

I have two pivot tables that seem to produce different results (in the summed numerical data), even though, as far as I can tell, they are identical in every other way.

One of them has been in the workbook for a very long time. That one is the incorrect one.

The other one, which I created a few minutes ago, is correct. I say "correct" in that it matches the totals that I get manually adding up the raw data.

I calculated the difference between the two for a particular item, and searched the raw data for that difference, but could not find it.

The number in the incorrect data is *higher* than in the correct data.

I realize that I should delete the old pivot table. But it's important that I find out the cause of the problem.

I don't think I can reproduce this problem (how could I?), but I am happy to look at any settings for the incorrect table that anyone suggests.

Anyone encountered something like this?

I'm in Excel 2007, in an .xlsx file, on Windows XP. The workbook contains a lot of vlookups, but is otherwise not very complicated.

 

[jasonb75]

Value field set to return Product instead of Sum?

 

[miconian]

Nope. It's set to sum.

 

[jasonb75]

What about the "Show values as" field? Is that set to "Normal" on both?

 

[miconian]

Both normal.

 

[jasonb75]

I would assume that you've checked all the obvious, but just to be sure :-

- all fields and any applied filters same on both tables.

- both tables sourcing the correct ranges. (options tab - change data source to view the source range).

- tried refreshing the pivottable.

 

[miconian]

One interesting thing about the incorrect table. It is showing an incomplete field list. But refreshing the pivot table doesn't help.

 

[jasonb75]

Are some of the missing fields actaully used in the "correct" table? If you remove them, do the results in both tables match?

 

[miconian]

jasonb75:

- all fields and any applied filters same on both tables.

This is correct. The missing field that I noticed on the incorrect table would not be selected in any case.

- both tables sourcing the correct ranges. (options tab - change data source to view the source range).

Slight difference. The correct table is sourcing based on a named range. (The name applies to a dynamic table that contains all the raw data). So the data source simply says:

Data2012

That's the name of the range.

The incorrect table is sourced to:

'2012 Data'!$A:$AJ

2012 Data is the name of the worksheet that has the raw data. The data is, indeed, fully contained between rows A and AJ.

I suppose that $A:$AJ indicates every single cell in those columns, whereas my named range does not. Perhaps there is some phantom data somewhere outside the table, but within those columns? But... I've looked carefully and sorted various ways, and I don't see it. The whole worksheet seems to be blank outside of my named range. In fact, I tried deleting all the rows outside the named range, and then refreshing the incorrect table, and it still had no effect.

- tried refreshing the pivottable.

This didn't help.

Also: the missing field is not selected, even on the correct table. It's just a coincidence that I noticed it was missing from the incorrect one as an option.

 

[jasonb75]

Could you post the formula used to define the range for the dynamic table?

Does the workbook contain sensitive data or could you send a copy if needed?