Excel Err Msg: Reference Is Not Valid

 Last reviewed: November 30, 1994
Article 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.

    -or-

  • 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

KBCategory: kberrmsg
KBSubcategory:

Additional reference words: 3.0 3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.