Microsoft KB Archive/178164

= XL97: Illegal Operation Refreshing Data in PivotTable =

Article ID: 178164

Article Last Modified on 10/22/2000

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q178164





SYMPTOMS
When you update data in a PivotTable, the following error message may appear:

This program has performed an illegal operation and will be shut down. If the problem persists, contact the program vendor.

If you click Details, you receive an error message similar to the following:

Excel caused an invalid page fault in module Kernel32.dll at 0137:bff9a5d0.



CAUSE
This problem occurs when all of the following conditions are true:


 * You create a PivotTable from external data, and then create a second PivotTable based on the first PivotTable. -and-


 * You use the GetPivotData worksheet function and reference both PivotTables. -and-


 * You update the data in either PivotTable.



WORKAROUND
To work around this problem, do not use a cell address for the name argument of the GetPivotData function when you use it. Instead, refer to an item label by name. For example, the following formulas refer to an item in each of the sample PivotTables that follow.

Example 1
Enter the following data for PivotTable1 in cells A1:D5.   Sum of Sales   Product Month         Widget1   Widget2   Grand Total Jan           $ 1,000   $ 500     $ 1,500 Feb           $ 1,200   $ 600     $ 1,800 Mar           $ 1,400   $ 700     $ 2,100 The following formula =GetPivotData(A1:D5, "Jan Widget2") returns the value 500, the value for Widget2 for the month of January.

The following formula =GetPivotData(A1:D5, "Jan") Returns the value 1,500, the total for the month of January.

Example 2
Enter the following data for PivotTable2 in cells F1:J11.    Sum of Sales     Product Month  Agent    Widget1   Widget2   Grand Total

Jan    Bill     $ 300     $ 400     $   700 Steve   $ 700     $ 100     $   800 Jan Total       $ 1,000   $ 500     $ 1,500

Feb    Bill     $ 800     $ 300     $ 1,100 Steve   $ 400     $ 300     $   700 Feb Total       $ 1,200   $ 600     $ 1,800

Mar    Bill     $ 600     $ 200     $   800 Steve   $ 800     $ 500     $ 1,300 Mar Total       $ 1,400   $ 700     $ 2,100 The following formula =GetPivotData(F1:J11, "Jan Bill Widget1") returns 300, the value for the month of January for the Agent Bill and for the Product Widget1.

The following formula =GetPivotData(F1:J11, "Feb Widget2") returns 1200, the value for Widget2 for the month of February.



STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.



MORE INFORMATION
The GetPivotData worksheet function returns data that is stored in a PivotTable. You can use GetPivotData to retrieve summary data from a PivotTable provided that the summary data is visible in the PivotTable. However, when you use the function to return data from more than one PivotTable, and one table is based on existing an PivotTable that uses external data, the program may quit when you update the data.

