XL4: Using Consolidate with Workbooks

The information in this article applies to:

• Microsoft Excel for Windows, version 4.0
• Microsoft Excel for the Macintosh, version 4.0

SUMMARY

In Microsoft Excel, worksheets bound in workbooks can be consolidated by using standard workbook referencing (that is, [Workbook Name]SheetName!Range). However, attempting to consolidate references that include wildcard characters results in the error message:

   Cannot open consolidation source file: '[Workbook
   Name]SheetName'!Range.

   ARTICLE-ID: Q118755
   TITLE     : XL5: Errors Using Wildcard in Sheet Name with Consolidate

MORE INFORMATION

When using the CONSOLIDATE() function or choosing Consolidate from the Data menu, a "?" and an "*" (without the quotation marks) are valid wildcard characters which can be used in references in place of single or multiple characters respectively. For example, to consolidate the range A1:D10 on SALES1.XLS and SALES2.XLS, valid references would be "SALES?.XLS!A1:D10" or "SALES*!A1:D10" (without the quotation marks).

If, however, SALES1.XLS and SALES2.XLS are bound sheets in a workbook, the use of these wildcards results in the error mentioned above. For the consolidation to work properly, it is necessary to use separate references for each bound worksheet. In the above example, valid references would be "[SALES.XLW]SALES1!A1:D10" and "[SALES.XLW]SALES2!A1:D10" (without the quotation marks).

Page 77 of "User's Guide 1" for version 4.0 states that the sheets can be unbound. Note, however, that the extended names don't work in consolidation references.

REFERENCES

"User's Guide 1," version 4.0, pages 374-383

"Function Reference," version 4.0, pages 62-63