XL Err Msg: 'Error in Formula' If Filename Starts with Number

ID: Q81318

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for OS/2, version 3.0
Microsoft Excel for the Macintosh, versions 3.x, 4.0, 5.0, 5.0a

SYMPTOMS

When you use a linked formula in Microsoft Excel, the formula will return the error message "Error in Formula" if either the following conditions is true:

The filename of the supporting worksheet begins with a number such as 1ABC.XLS.

-or-

The worksheet name in the formula is not enclosed in apostrophes (').

WORKAROUND

To avoid receiving this error message and to properly link the worksheets, use any of the following methods:

Method 1: On the dependent sheet, type your formula as

= '[<workbook>]<sheet>'!<reference>

where <workbook> and <sheet> are the workbook and sheet names enclosed in apostrophes, and <reference> is the cell or range reference.

NOTE: If you are using Microsoft Excel version 4.x or earlier, use this formula:

='<Filename.xls>'!<reference>

Method 2: If the filename (workbook and sheet) is used as a defined name in a function, then you can use the following formula

=INDIRECT("'"&<filename>&"'!<reference>")

where <filename> is a defined name and <reference> is a cell or range of cells on the sheet.

Method 3: If you are using Microsoft Excel version 3.0, follow the "Creating a linking formula that uses functions" procedure described on page 311 of the "User's Guide," version 3.0. For Microsoft Excel version 3.0 for the Macintosh, see page 305 of the "User's Guide."

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

"User's Guide," version 4.0, pages 364-369
"Microsoft Excel User's Guide," for Windows, version 3.0, pages 311-313
"Microsoft Excel User's Guide," for the Macintosh, version 3.0, page 305

Additional query words: 2.10 2.1 2.10c 2.1c 2.10d 2.1d 3.0 err msg

Keywords :
Version :
Platform :
Issue type :