XL97: Defined Names Display #REF! After Clicking Undo

• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, if you insert or delete one or more rows or columns in a worksheet, and then click Undo on the Edit menu, some of the defined names in the worksheet may contain #REF! errors.

CAUSE

This problem may occur if all of the following conditions are true:

• You insert or delete one or more rows at the top of the worksheet (row 1) or you insert or delete one or more columns on the left side of the worksheet (column A).

      -and-
  

• You click Undo on the Edit menu to remove the inserted rows or columns or to restore the removed rows or columns.

      -and-
  

• One or more of the defined names in the worksheet contains a mixed reference, a reference in which the row is absolute and the column is relative, or vice versa. For example, the following formula contains a mixed reference:

        =Sheet1!$B5
  

RESOLUTION

To correct this problem, install Microsoft Excel 97 Service Release 1 (SR-1).

To work around this problem, manually correct the affected defined names. To do this, point to Name on the Insert menu, and click Define. You can then edit defined names in the Define Name dialog box.

To prevent the problem from occurring in Microsoft Excel 97, do not use mixed references in defined names.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 97. This problem was corrected in Microsoft Excel 97 SR-1.

For additional information about SR-1, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q172475
   TITLE     : OFF97: How to Obtain and Install MS Office 97 SR-1

MORE INFORMATION

This problem may occur under the following different sets of conditions:

• If you delete or insert a row starting at row 1, and then click Undo on the Edit menu, any defined name that contains a mixed reference in which the row number is relative is affected. For example, if a defined name refers to the following columns and rows

        =Sheet1!$B5
  

  the reference is changed to the following:

        =Sheet1!#REF!
  

• If you delete or insert a column starting at column A, and then click Undo on the Edit menu, any defined name that contains a mixed reference in which the column letter is relative is affected. For example, if a defined name refers to the following columns and rows

        =Sheet1!B$5
  

  the reference is changed to the following:

        =Sheet1!#REF!