GetPivotData() Functions in Excel XP
February 04, 2004 - by Bill Jelen
Samantha W writes with an annoying problem in Excel XP:
I recently upgraded to Excel XP from Excel 2000. There is one behaviour that is driving me crazy. I create a pivot table. Next to the pivot table, I enter a formula that points at a cell inside the pivot table. When I then copy this formula down to the next row(s), the formula keeps pointing at the original cell.
Yes! This is incredibly annoying. If you enter a formula that seems like it should be =H10, Excel replaces it with this formula:
Of course, as you copy this formula, it keeps pointing at the original cell.
I tried a few variations:
- If I type an equals sign and use the mouse to touch H10, I get the strange formula.
- If I type an equals sign and use the arrow keys to navigate to H10, I get the strange formula
If I just type
=H10, then I get a formula which can copy
Samantha later found a setting to turn off this annoying behavior: You can choose Tools>Customize, and from the Data category on the Commands tab, add the Generate GetPivotData command to one of your toolbars. Use this button to toggle the feature on and off.