Microsoft KB Archive/126100

= Consolidated Labels Use Name of Workbook Instead of Sheet =

Article ID: 126100

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q126100





SYMPTOMS
In Microsoft Excel, when you use the Consolidate dialog box to consolidate data on worksheets that are contained in the same workbook, if you choose to display the left column labels, and you click the "Create Links To Source Data" check box, summary labels in the left column display the name of the workbook instead of the sheet name.



CAUSE
This behavior occurs because the Consolidate dialog box does not determine that the consolidated data is contained on worksheets in the same workbook and that the workbook name does not distinguish where the data is located.

For example, if you consolidate data from Sheet1 and Sheet2 in BOOK1, and you click the Left Column check box (under Use Labels In) and the Create Links To Source Data check box, the consolidated data may appear as follows:

  A1:         B1: BOOK1   C1: 1000 A2:        B2: BOOK1   C2: 1000 A3: sales  B3:         C3: 2000



WORKAROUND
To work around this behavior, on each of the worksheets that you are consolidating, create a local defined name called sheet_title that refers to a cell that contains the worksheet name or the title that you want to appear in the consolidated data. To do this, follow these steps:


 * 1) Select a worksheet that contains data that you want to consolidate.
 * 2) In an empty cell on the worksheet, enter the worksheet name or any other title that you want to appear in the consolidated data.
 * 3) Select the cell that contains the data that you entered in step 2.
 * 4) On the Insert menu, point to Name and then click Define. In the Names In Workbook box, type "SHEET1!sheet_title" where SHEET1 is the name of the selected worksheet.
 * 5) Click OK.

Repeat steps 1-5 with each worksheet that contains data that you want to consolidate.



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



MORE INFORMATION
When you use the Consolidate dialog box, the Use Labels In check box allows you to choose whether to use labels from the top row of the source areas, from the left column, both, or neither. These check boxes are meaningful only when you consolidate data by category. Multiple source areas must be laid out and labeled identically for this option to work accurately. When you consolidate data by category, be sure to include the category labels in the source areas you select for your consolidation. Microsoft Excel transfers these labels to the destination area for you.

The "Create Links To Source Data" check box creates links from the destination area to the source areas when you consolidate data so that the destination area will be automatically updated whenever the source data changes. Microsoft Excel creates a linking formula for every cell and inserts rows or columns into the destination area to hold the linking formulas for each piece of source data. The destination area is then grouped with linking formulas placed in hidden rows or columns subordinate to positions or categories in the destination area.



Excel 97
For more information about consolidating data, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel Help, type the following text

  consolidating data

and then double-click the selected text to go to the "Consolidate Data (Microsoft Excel)" topic. If you are unable to find the information you need, ask the Office Assistant.

Excel 5.0
"Microsoft Excel User's Guide," version 5.0, pages 528-535

For an overview of consolidating data, click the Search button in Help and type:

  consolidating data

Additional query words: 5.00c XL

Keywords: kbbug kbnofix KB126100

-

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

© Microsoft Corporation. All rights reserved.