XL5: Relative Reference in Name Lost Saving to 4.0 Format

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel version 5.0, if you save a workbook that contains a defined name, and the defined name refers to a formula that contains a relative reference, the reference in the defined name is no longer relative when you save the file to the Microsoft Excel 4.0 Worksheet or Workbook file format, and open the file in Microsoft Excel version 4.0.

CAUSE

This behavior occurs if you create a name that refers to a formula that contains a relative reference. For example, this behavior occurs if you create the name TEST that refers to the following formula, relative to cell A1 on a worksheet:

   =SUM(Sheet1!B1)

However, in Microsoft Excel version 5.0, if you save this file to the Microsoft Excel 4.0 Worksheet or Workbook format, and then open the file in Microsoft Excel version 4.0, the formula in the defined name behaves as an absolute reference, even though the name appears to refer to a relative reference. For example, the value in each cell that contains the name defined above is the sum of the values in cell B1, or the value of the following reference

   =SUM(Sheet1!$B$1)

WORKAROUNDS

To avoid this behavior, do either of the following:

• Create a defined name that contains a relative reference without including the reference in a formula. For example, the problem does not occur when you create a name that refers to the following:

        =SHEET1!B1
  

  -or-

• When you open the file in Microsoft Excel version 4.0, you can correct the incorrect references by using the Define Name dialog box with the following steps:

  1. From the Formula menu, choose Define Name.

  2. From the Names in Sheet list, select the name that you want to

        correct.
  

  3. Choose the Add button.

  4. Choose Close.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in a later version of Microsoft Excel.