Microsoft KB Archive/284870

= #REF! Is returned when you create a reference to a custom subtotal in PivotTable in Excel =

Article ID: 284870

Article Last Modified on 2/8/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



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(,&quot;[;Data,]&quot;) where
 *  is an absolute reference to the top left cell in the PivotTable.
 *  is the name of the data group for which the subtotal is being calculated.
 *  is the name of the particular item being subtotaled.
 *  is the name of the function being used in the subtotal, such as SUM or AVERAGE.

Delete &quot;Data&quot; to create the correct formula, which is as follows:   GETPIVOTDATA(,&quot;<GroupName>[<GroupItem>;<FunctionName>]&quot;)

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

MORE INFORMATION
This behavior occurs only when you use Custom Subtotals; it does not occur when you use Automatic Subtotals.

Additional query words: XL2002 XL2003 XL2007

Keywords: kbbug kbpending KB284870

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.