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

• 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>

          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.

          "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