XL97: Illegal Operation Refreshing Data in PivotTable

• Microsoft Excel 97 for Windows

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.

   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

   =GetPivotData(A1:D5, "Jan Widget2")

The following formula

   =GetPivotData(A1:D5, "Jan")

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

   =GetPivotData(F1:J11, "Jan Bill Widget1")

The following formula

   =GetPivotData(F1:J11, "Feb Widget2")

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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.

REFERENCES

For more information about the GetPivotData function, click the Index tab in Help, type the following text

   getpivotdata