Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. #REF! Is returned when you create a reference to a custom subtotal in PivotTable in Excel

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

View products that this article applies to.

This article was previously published under Q284870

↑ Back to the top

Symptoms

When you create a reference to a cell that contains a custom subtotal in a PivotTable, #REF! is unexpectedly returned.

↑ Back to the top

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.

↑ Back to the top

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>]")

↑ Back to the top

Status

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

↑ Back to the top

More information

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

↑ Back to the top

Keywords: KB284870, kbpending, kbbug

↑ Back to the top

Article Info
Article ID : 284870
Revision : 4
Created on : 2/8/2007
Published on : 2/8/2007
Exists online : False
Views : 250