XL98: Consolidated Labels Use Name of Workbook Instead of Sheet (192896)
The information in this article applies to:
- Microsoft Excel 98 Macintosh Edition
This article was previously published under Q192896 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:
- Select a worksheet that contains data that you want to consolidate.
- In an empty cell on the worksheet, enter the worksheet name or any
other title that you want to appear in the consolidated data.
- Select the cell that contains the data that you entered in step 2.
- 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.
- 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 Microsoft Excel 98
Macintosh Edition.
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.
| Modification Type: | Major | Last Reviewed: | 9/11/2002 |
|---|
| Keywords: | kbbug KB192896 |
|---|
|