Microsoft Knowledge Base

WINDOWS               | MACINTOSH

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In the versions of Microsoft Excel listed above, when you open a Microsoft Excel version 4.0 worksheet, or when you rename a worksheet in your workbook, a name contained in the workbook may be redefined with an incorrect reference or formula.

CAUSE

This behavior occurs if the name refers to a worksheet in the workbook, and the length of the worksheet name is increased. If the name is defined as a long formula that refers to a worksheet in the workbook, and the worksheet name is increased, the reference may exceed the allowed length, and the reference may be truncated.

The length of a worksheet name may increase when you open a Microsoft Excel version 4.0 worksheet in Microsoft Excel versions 5.0 and 7.0, because the worksheet is opened as a workbook that contains a worksheet with the same name as the workbook file. If the filename is long, this error may occur. This problem may also occur if you rename the sheet that the name refers to using the Rename Sheet dialog box.

For example, if you create a name for the following formula

   =IF(ISERROR(SHEET1!A1),"",IF(AND(SHEET1!B3>5,SHEET1!A3=1),
    ROUND((SHEET1!B3-SHEET1!A3)/SHEET1!A3%,0),
    IF(AND(SHEET1!A3>1,SHEET1!B3>5),ROUND(SHEET1!B3,1),"")))

   =ISERROR('this is a long sheet name'!A1)o"" AND('this is a long sheet
    name'!B3>5,'this is a long sheet name'!A3=1) ROUND(('this is a long
    sheet name'!B3-'this is a long sheet name'!A3)/'this is a long sheet
    name'!A3%,0)n

WORKAROUNDS

To work around this problem, rename your sheet back to the original name, or to a name that contains the number of characters equal to or fewer than the number of characters in the original sheet name.

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

For more information about Naming A Cell, Range, Or Formula, choose the Search button in Help and type:

   defining, names