XL5: Errors Using Wildcard in Sheet Name with Consolidate

ID: Q118755

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you use a wildcard character in a sheet name in the Consolidate dialog box, you receive the following error messages

Cannot open consolidation source file '[BOOK1.XLS]sheet*'

Cannot find 'SHEET*'

Cannot open consolidation source file 'SHEET*'

No data was consolidated

where BOOK1.XLS is the name of the workbook that contains the worksheets that you want to consolidate, and SHEET* is the reference that you entered to consolidate all of the worksheets that begin with the word SHEET.

Additionally, when you choose Consolidate again from the Data menu, the reference that you entered is listed twice in the All References list; once with the worksheet name in capitals, and once in lowercase, as in the following example:

'sheet*'!$A$1
'SHEET*'!$A$1

CAUSE

This behavior occurs because you cannot use the asterisk (*) or question mark (?) wildcard character to consolidate multiple sheets in a workbook. This behavior is by design of Microsoft Excel. However, seeing multiple error messages and multiple occurrences of the sheet reference may be confusing. This problem occurs only when you include the book name in the Reference box in the Consolidate dialog box, and then use the Add button, instead of the OK button, to enter the reference.

WORKAROUND

To avoid receiving these error messages, and having the sheet reference with the wildcard character listed twice, do either of the following:

Use a separate reference for each worksheet that you want to consolidate. For example, to consolidate the data in cells A1:D10 on Sheet1 and Sheet2 located in BOOK1.XLS, use the following references:
[BOOK1.XLS]SHEET1!A1:D10
[BOOK1.XLS]SHEET2!A1:D10

Use 3D references to consolidate data within a workbook. For example, to sum the values in cells A1:D10 on Sheet1 and Sheet2 located in BOOK1.XLS, use the following formula:
```
=SUM(SHEET1:SHEET2!A1:D10) 
```

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

Note that in Microsoft Excel version 5.0c, you still cannot consolidate multiple sheets in a workbook using the asterisk (*) or question mark (?) wildcard character. However, when you attempt this, you do not receive the duplicate "Cannot open consolidation source file..." error message, or the "Cannot find..." error message (the other error messages appear), and the reference is only listed once in the All References list.

REFERENCES

For an Overview Of Consolidating Data, choose the Search button in Help and type:

consolidating data

Additional query words: err msg

Keywords :
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type :