GETPIVOTDATA formula is automatically created when you try to create simple link in Excel

When you try to create a simple cell link formula that refers to a cell in the data area of a PivotTable in Microsoft Excel, 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 feature is enabled by default in Microsoft Excel 2002 and in Microsoft Office Excel 2003.

This behavior also occurs when the Generate GetPivotData setting in the PivotTable group on the Options tab is turned on (enabled). This feature is enabled by default in Microsoft Office Excel 2007.

For more information about the GETPIVOTDATA function, see the “More Information” section.

RESOLUTION

If you want to disable the Generate GetPivotData setting, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Excel 2000, Excel 2002, and Excel 2003

1. On the Tools menu, click Customize.
2. Click the Toolbars tab, and then click to select the PivotTable check box. The PivotTable toolbar is displayed.
3. Click Close.
4. On the PivotTable toolbar, click the Toolbar Options arrow. This arrow appears 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. When you click this button, it is turned on or off. If the button is selected, the GETPIVOTDATA formula is automatically generated.

Excel 2007

1. Click the Microsoft Office Button, click Excel Options, and then click the Formulas tab.
2. Make sure that the Use GetPivotData functions for PivotTable references check box is selected, and then click OK.
3. Click in the worksheet.
4. Click the Options tab, click Options in the PivotTable group, and then click to clear the Generate GetPivotData check box.

WORKAROUND

To work around the Generate GetPivotData feature, follow these steps:

1. Select a cell outside 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. However, your link will not point to the new location.


APPLIES TO
Microsoft Office Excel 2007
Microsoft Office Excel 2003
Microsoft Excel 2002 Standard Edition

——————————————–

Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
Microsoft Corporation. All rights reserved. Terms of Use | Trademarks

 


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image