XL2000: Calculating Worksheet with Hidden Data Results in Zeroes

ID: Q235080

The information in this article applies to:

Microsoft Excel 2000
Microsoft Office Spreadsheet Component 9.0, run time

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:

In the Office Spreadsheet object, click the arrow in the column that contains the AutoFilter data field.

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.

On the File menu, click Save as Web Page.

In the Save dialog box, click Selection:$A$1:$B$10 and click to select the Add interactivity check box.

Click Publish.

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.

In your browser, select cell A1. Click the AutoFilter button on the Office Spreadsheet component toolbar.

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

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

REFERENCES

For more information about the AutoFilter command, click Microsoft Excel Help on the Help menu, type "AutoFilter" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about subtotals, click Microsoft Excel Help on the Help menu, type "subtotals" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about the SUMIF function, click Microsoft Excel Help on the Help menu, type "SUMIF" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: XL2000

Keywords :
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug