XL2002: When You Create a Reference to a Custom Subtotal in PivotTable, #REF! Is Returned (284870)
The information in this article applies to:
This article was previously published under Q284870 SYMPTOMS
When you create a reference to a cell that contains a custom subtotal in a PivotTable, #REF! is unexpectedly returned.
CAUSE
When a reference is created to a cell in a PivotTable, Microsoft Excel automatically generates a GETPIVOTDATA formula in the cell in which the reference is made. If the referenced PivotTable cell contains a custom subtotal, the GETPIVOTDATA formula is incorrect.
WORKAROUND
To work around this issue, edit the incorrect reference so that your GETPIVOTDATA formula displays the correct result. The automatically generated formula has the following form
GETPIVOTDATA(<AnchorCell>,"<GroupName>[<GroupItem>;Data,<FunctionName>]")
where
- <AnchorCell> is an absolute reference to the top left cell in the PivotTable.
- <GroupName> is the name of the data group for which the subtotal is being calculated.
- <GroupItem> is the name of the particular item being subtotaled.
- <FunctionName> is the name of the function being used in the subtotal, such as SUM or AVERAGE.
Delete "Data" to create the correct formula, which is as follows:
GETPIVOTDATA(<AnchorCell>,"<GroupName>[<GroupItem>;<FunctionName>]")
STATUSMicrosoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. MORE INFORMATION
This behavior occurs only when you use Custom Subtotals; it does not occur when you use Automatic Subtotals.
| Modification Type: | Major | Last Reviewed: | 8/3/2001 |
|---|
| Keywords: | kbbug KB284870 |
|---|
|