Microsoft KB Archive/214364

= XL2000: Label Is Missing from the Upper-Left Corner of the Consolidation Table =

Article ID: 214364

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214364





SYMPTOMS
If you use the Consolidate command to summarize data from two or more worksheets, the label may be missing from the upper-left corner of the consolidation table. For example, the table may appear as follows             Number Alpha    5 Bravo    7 Charlie  9 instead of:   Name      Number Alpha    5 Bravo    7 Charlie  9



CAUSE
This problem occurs when you create the consolidation table and you click to select both the Top row and Left column check boxes under Use labels in within the Consolidate dialog box.



WORKAROUND
To work around this problem, follow these steps:
 * 1) Select the consolidation table.
 * 2) On the Data menu, click Consolidate.
 * 3) Click to clear both the Top row and the Left column check boxes, and then click OK.
 * 4) On the Data menu, click Consolidate, click to select the Top row check box, and then click OK.
 * 5) On the Data menu, click Consolidate. Click to select the Left column check box, and then click OK.

The label should now appear correctly in the upper-left corner of the consolidation table.

To prevent this problem from occurring when you create a new consolidation table, select only one of the two check boxes (Top row or Left column) when you create the table. Next, click Consolidate on the Data menu, select the other check box, and then click OK.



STATUS
Microsoft has confirmed that this is a problem in Excel 2000.



MORE INFORMATION
The Consolidate feature in Microsoft Excel allows you summarize data from multiple, identically arranged tables. To see an example that uses the Consolidate feature and demonstrates the problem described in the "Symptoms" section, follow these steps:   In Microsoft Excel, create a new workbook. Type the following values in the specified worksheets:   Sheet1                     Sheet2 A1: Name     B1: Number   A1: Name      B1: Number A2: Alpha    B2: 1        A2: Alpha     B2: 4 A3: Bravo    B3: 2        A3: Bravo     B3: 5 A4: Charlie  B4: 3        A4: Charlie   B4: 6  Click Sheet3, and then select cell A1.  On the Data menu, click Consolidate. In the Consolidate dialog box, follow these steps: <ol style="list-style-type: lower-alpha;"> Click Sheet1. Select the cell range A1:B4, and then click Add.</li> Click Sheet2. Select the cell range A1:B4, and then click Add.</li> Click to select both the Top row and the Left column check boxes in the dialog box, and then click OK.</li></ol>

The following consolidation table is created in Sheet3: <pre class="fixed_text">  A1:           B1: Number A2: Alpha    B2: 5 A3: Bravo    B3: 7 A4: Charlie  B4: 9 </li></ol>

Note that the values in the table (5, 7, 9) are the sums of the respective values in cells B2, B3, and B4 in the tables in Sheet1 (1, 2, 3) and Sheet2 (4, 5, 6).

In addition, the label "Name" is missing from cell A1. If you follow the steps in the "Workaround" section, the missing label appears correctly, as in the following example: <pre class="fixed_text">  A1: Name      B1: Number A2: Alpha    B2: 5 A3: Bravo    B3: 7 A4: Charlie  B4: 9

Additional query words: XL2000

Keywords: kbbug kbpending KB214364

-

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

© Microsoft Corporation. All rights reserved.