XL4: Using Consolidate with Workbooks

ID: Q87880

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.

For information about a similar issue in Microsoft Excel 5.0, please see the following article(s) in the Microsoft Knowledge Base:

Q118755 : 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

Additional query words:

Keywords :
Version :
Platform :
Issue type :