Excel Err Msg: Reference Is Not Valid

ID: Q75734

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0
Microsoft Excel for OS/2, version 3.0
Microsoft Excel for the Macintosh, versions 3.0, 4.0

SUMMARY

Microsoft Excel 3.0 and 4.0 have a Consolidation feature that allows you to summarize large amounts of data from several worksheets. When specifying source areas, you can use the asterisk (*) and question mark (?) as wildcard characters in the filename. However, using these wildcard characters in conjunction with defined names may result in the following error message:

Reference is not valid

MORE INFORMATION

There is a potential danger in using wildcards in conjunction with defined names because there may be certain sheets that are not intended to be part of the consolidation that DO satisfy the wildcard but do NOT contain the defined name specified.

Example

Suppose your source sheets have labels which all start with SHEET and each of these sheets has a range named "Data" defined on it. In the Reference section of the Data Consolidate dialog box, you would enter the following as your source: SHEET*.XLS!Data (in Excel for Windows) SHEET*!Data (in Excel for the Macintosh)
This entry will find any worksheet in the current directory beginning with the letters "SHEET" and look for a defined name called "Data." The problem occurs when the current directory contains a file that satisfies the wildcard, such as SHEET4.XLS in Excel for Windows or SHEET4 (without the extension) in Excel for the Macintosh. Excel won't be able to find the defined name "Data" on that sheet and returns the error message.

Solution

If you get the error message "Reference is not valid" when you are using wildcards AND defined names at the same time, do one of the following:

Verify that the current directory does not contain any files that match the wildcard from which you do not want to consolidate.

Modify the wildcard so that no other files match the wildcard(s), except the ones from which you want to consolidate.

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 381-382

"Microsoft Excel User's Guide," version 3.0, page 333

Additional query words: 3.0

Keywords :
Version :
Platform :
Issue type :