XL2000: Consolidated Labels Use Name of Workbook Instead of Sheet Name

This article was previously published under Q214283
This article has been archived. It is offered "as is" and will no longer be updated.
In Microsoft Excel 2000, summary labels may display the name of the workbook instead of the name of the worksheet. This behavior can occur when you do the following:
  • You use the Consolidate dialog box to consolidate data on worksheets that are contained in the same workbook.
  • You choose to display the left column labels.
  • You click to select the Create Links To Source Data check box. Under these conditions, summary labels in the left column may display the name of the workbook instead of the name of the worksheet.
This behavior can occur 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, andyou click to select 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				
To work around this behavior, on each of the worksheets that you areconsolidating, create a local defined name of "sheet_title" that refersto a cell that contains the worksheet name or the title that you want toappear 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, type 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
    where SHEET1 is the name of the selected worksheet.
  5. Click OK.
Repeat steps 1 through 5 with each worksheet that contains data that you want to consolidate.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
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, from 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 thedestination area to the source areas when you consolidate data so that thedestination area will be automatically updated whenever the source datachanges. Microsoft Excel creates a linking formula for every cell andinserts rows or columns into the destination area to hold the linkingformulas for each piece of source data. The destination area is thengrouped with linking formulas placed in hidden rows or columns subordinateto positions or categories in the destination area.
For more information about consolidating data, click Microsoft Excel Help on the Help menu, type consolidating data in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Article ID: 214283 - Last Review: 10/14/2013 15:29:44 - Revision: 3.1

Microsoft Excel 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbpending KB214283