Article ID: 207663
Article Last Modified on 6/20/2001
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q207663
SYMPTOMS
When you update data in a PivotTable, you may receive an error message similar to the following:
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 may 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, use a name as the argument of the GetPivotData function. 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 that this is a problem in the Microsoft products that are 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 an existing PivotTable that uses
external data, the program may quit when you update the data.
REFERENCES
For more information about the GetPivotData function, click Microsoft Excel Help on the Help menu, type getpivotdata worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Additional query words: XL97 ipf gpf crash sql server dbf oracle
Keywords: kberrmsg kbbug kbpending KB207663