GETPIVOTDATA formula is automatically created when you try to create simple link in Excel 2002 and in Excel 2003 (287736)



The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002

This article was previously published under Q287736

SYMPTOMS

When you try to create a simple cell link formula that refers to a cell in the data area of a PivotTable, a GETPIVOTDATA formula is automatically created instead.

CAUSE

This behavior occurs when the Generate GetPivotData setting on the PivotTable toolbar is turned on (enabled). This Microsoft Excel feature is turned on by default in Microsoft 2002 and Microsoft Office Excel 2003.

For more information about the GETPIVOTDATA function, see the "More Information" section of this article.

RESOLUTION

If you want to turn off the Generate GetPivotData setting, follow these steps:
  1. Select Customize on the Tools menu.
  2. Click the Toolbars tab, and then select the PivotTable check box. The PivotTable toolbar is displayed.
  3. Click Close.
  4. On the PivotTable toolbar, click the Toolbar Options arrow (on the right end of the toolbar).
  5. Click Add or Remove Buttons, click PivotTable, and then select Generate GetPivotData.
  6. Click in the worksheet.
  7. Click the Generate GetPivotData button that now appears on the PivotTable toolbar. Clicking this button turns it on or off. If the button is selected, the GETPIVOTDATA formula will be automatically generated.

WORKAROUND

To work around the Generate GetPivotData feature, follow these steps:
  1. Select a cell outside of the PivotTable range.
  2. Type an equal sign (=).
  3. Inside the PivotTable, type the cell address that contains the value that you want to reference. For example, if the value is in cell C3, type C3.
  4. Press ENTER.

MORE INFORMATION

To make sure that the information that is returned is correct even if the PivotTable is updated, you can use the GETPIVOTDATA formula, instead of a simple link into a PivotTable. For example, if you use a simple link to a summation cell in a PivotTable, and then you update the PivotTable, the summation cell may move, but your link will not point to the new location.

REFERENCES

For more information about about the GETPIVOTDATA function, click Microsoft Excel Help on the Help menu, type GETPIVOTDATA in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Modification Type:MajorLast Reviewed:3/15/2005
Keywords:kbPivotTable kbprb KB287736