XL97: Copying Worksheet into Workbook Deletes Defined Name

ID: Q190204

The information in this article applies to:

SYMPTOMS

When you copy a worksheet into a workbook, existing defined names may be deleted. Formulas that refer to the deleted defined names return the #NAME? error.

CAUSE

This problem occurs if the following conditions are true:

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows.

MORE INFORMATION

Steps to Illustrate the Problem

Use the following steps to see an example of the problem.

NOTE: This example assumes that the two workbooks that you are working with are named Book1 and Book2.

1. Start Microsoft Excel.

2. On the Insert menu, point to Name, and then click Define. Type

   "Testname" (without the quotation marks) in the Names In Workbook box,
   and then click OK.

   Note that the defined name "Testname" is created in Book1.

3. Click the New button on the Standard toolbar to create a new workbook
   named Book2.

4. Type "=Testname" (without the quotation marks) into cell A1, and then
   press ENTER.

   Note that the formula results in the #NAME? error because Testname does
   not exist in Book2.

5. Type "1" (without the quotation marks) into cell A5, and then press
   ENTER.

6. Select cell A5. On the Insert menu, click Chart. Click Finish in the
   Chart Wizard - Step 1 of 4 - Chart Type" dialog box.

   Note that a chart is created on Sheet1 in Book2.

7. On the Edit menu, click Move Or Copy Sheet. In the Move Or Copy dialog
   box, click the arrow in the To Book box, and select Book1. Click to
   select the Create A Copy check box, and then click OK.

   Note that Sheet1 is copied into Book1 as Sheet1 (2).

8. On the Insert menu, point to Name, and then click Define.

   Note that Testname no longer appears in the Names In Workbook box.

Additional query words: XL97
Keywords          : kbdta xlui xlformula 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: November 6, 1998