Microsoft KB Archive/127937

= Microsoft Knowledge Base =

XL: Incorrect Reference in Name After File Open/Rename Sheet
Last reviewed: March 27, 1997

Article ID: Q127937

5.00 5.00c 7.00 7.00a | 5.00 5.00a

WINDOWS              | MACINTOSH kbother

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),""))) if the name of SHEET1 becomes 'this is a long sheet name,' the resulting formula may appear similar to the following:

=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 Note that the formula is truncated, and contains some incorrect characters.

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.