Microsoft KB Archive/235080

= XL2000: Calculating Worksheet with Hidden Data Results in Zeroes =

Article ID: 235080

Article Last Modified on 10/7/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Office Spreadsheet Component 9.0, run time

-



This article was previously published under Q235080



SYMPTOMS
When you publish a Microsoft Excel worksheet to an interactive Web page, if you recalculate the Microsoft Office Spreadsheet component and cells contain SUBTOTAL formulas, the result may be displayed as zero (0).



CAUSE
This problem occurs when all of the following conditions are true:


 * You create the Office Spreadsheet component in a Microsoft Excel 2000 spreadsheet.
 * The spreadsheet contains totals created with the SUBTOTAL function.
 * The rows containing the detail information for the SUBTOTAL are hidden.
 * You save the file as a Web page with interactivity enabled.
 * You open the Web page in your browser, and apply an AutoFilter in the Office Spreadsheet component.
 * You calculate the Office Spreadsheet component by doing either of the following:
 * You click Calculate Now in the Spreadsheet Property Toolbox.

-or-
 * You press F5.



RESOLUTION
To resolve this issue, use either of the following methods.

Method 1: Unhide the Data Field Items in AutoFilter
To unhide the data field items, follow these steps:
 * 1) In the Office Spreadsheet object, click the arrow in the column that contains the AutoFilter data field.
 * 2) In the drop-down list, click to select (place a check mark in) any hidden data items. Click OK.

The totals and grand totals are displayed as expected.

Method 2: Use SUMIF and SUM Instead of SUBTOTAL
Instead of using the SUBTOTAL function, use the SUMIF and SUM functions as in the following example:

  In Excel, type the following into a worksheet:   A1 : Item            B1 : Amount A2 : A              B2 : 100 A3 : A              B3 : 200 A4 : B              B4 : 300 A5 : B              B5 : 400 A6 : A              B6 : 500 A7 : B              B7 : 600 A8 : Total A        B8 : =SUMIF($A$2:$A$7,"A",$B$2:$B$7) A9 : Total B        B9 : =SUMIF($A$2:$A$7,"B",$B$2:$B$7) A10: Grand Total    B10: =SUM($B$8:$B$9)  Select cells A2:B7. On the Data menu, point to Group and Outline and click Group. In the Group dialog box, click OK. On the Data menu, point to Group and Outline and click Hide Detail. Select cells A1:B10.</li> On the File menu, click Save as Web Page.</li> In the Save dialog box, click Selection:$A$1:$B$10 and click to select the Add interactivity check box.</li> Click Publish.</li> Check to select the Open published web page in browser check box. Click Publish.

Your browser opens the Web page containing the Office Spreadsheet component.</li> In your browser, select cell A1. Click the AutoFilter button on the Office Spreadsheet component toolbar.</li></ol>

If you recalculate the Office Spreadsheet component, the values should now display as expected.

<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="references_section">