XL97: Illegal Operation Refreshing Data in 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.