XL97: Defined Names Display #REF! After Clicking Undo
ID: Q171898
|
The information in this article applies to:
-
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:
Q172475 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!
Additional query words:
XL97 sr1
Keywords :
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug