XL98: Hard Disk Name Appears in Formula or Defined Name When You Copy or Move a Worksheet (180617)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q180617

SYMPTOMS

In Microsoft Excel 98 Macintosh Edition, if you copy a worksheet, the following problems may occur.

Case 1

A linking formula may be changed so that the name of the hard disk appears in the formula.

For example, if a formula should appear as follows 
   =[Workbook1]Sheet1!A1
it actually appears as follows: 
   ='Freds DiskDes:ktop Folder:[Workbook1]Sheet1'!A1
As a result, the formula does not return the correct result.

Case 2

Defined names may be changed so that the name of the hard disk appears in the formula.

For example, if a defined name should refer to the following 
   =[Workbook1]Sheet1!A1
it actually refers to the following: 
   ='Freds DiskDes:ktop Folder:[Workbook1]Sheet1'!A1
To see an example of these problems, please see the "More Information" section in this article.

CAUSE

Case 1

The first problem (in which a formula is changed) may occur when the following conditions are true:

  • The workbook to which the formulas are linked (for example, Workbook1) is stored on a floppy disk drive, hard disk drive, network drive (shared disk), or random access memory (RAM) disk, and the disk name is exactly ten characters in length, including spaces. For example, the disk is named either of the following: 
          Freds Disk
      MyPowerMac
    -and-

  • You copy a worksheet that contains links to the workbook, either by clicking Move Or Copy Sheet on the Edit menu or by pressing CONTROL and clicking the worksheet tab, and then clicking Move Or Copy on the shortcut menu.

    It does not matter which workbook contains the worksheet you are copying. What matters is that the formulas are linked to the specific workbook (Workbook1, in this example).
NOTE: This problem does not occur when you move a worksheet.

Case 2

The second problem, (in which a defined name is changed) may occur when the following conditions are true:

  • The workbook to which the formulas are linked (for example, Workbook1) is stored on a floppy disk drive, hard disk drive, network drive, or RAM disk, and the disk name is exactly ten characters in length, including spaces. For example, the disk is named either of the following: 
          Freds Disk
      MyPowerMac
    -and-

  • You copy a different worksheet in the same workbook that contains the defined name.

    For example, if the defined name refers to a cell in Sheet1, you copy Sheet2 or Sheet3, and then copy the worksheet into another workbook.
NOTE: This problem does not occur when you move a worksheet.

RESOLUTION

To prevent these problems from occurring, make sure that the names of your hard disk drive, floppy drive, or RAM disk are not exactly ten characters in length.

If any of your network drives have names that are exactly ten characters in length, contact your network administrator to see if it is possible to rename the network drive. Otherwise, store your workbooks on a different network drive, or on a local hard disk with a name that is not ten characters in length.

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.

MORE INFORMATION

Examples

Case 1: Linking Formulas Are Changed

To see an example of this behavior, follow these steps:

  1. Quit all running programs.
  2. In the Finder, select the icon for your hard disk. Click the title once. Type Freds Disk and press RETURN.
  3. Start Microsoft Excel. Note that the program may take longer than usual to start; this is because Microsoft Excel must re-register itself after you rename the hard disk.
  4. In Sheet1, enter the value 100 in cell A1.
  5. In Sheet2, enter the following formula in cell A1: 
          =Sheet1!A1
  6. On the File menu, click Save. Click the Desktop button. Change the file name to Workbook1 and click Save.
  7. Do either of the following:

    1. With Sheet2 still active, click Move Or Copy Sheet on the Edit menu. Select the Create A Copy checkbox and click OK.
    -or-

    1. Create a new workbook. In Sheet1 of the new workbook, enter the following formula in cell A1: 
                =[Workbook1]Sheet1!A1
    2. On the Edit menu, click Move Or Copy Sheet. Select the Create A Copy checkbox and click OK.
The formula in cell A1 in the new worksheet appears as follows: 
>
   ='Freds DiskDes:ktop Folder:[Workbook1]Sheet1'!A1
If you change the value in cell A1 in Sheet1, the formula in Sheet2 is updated correctly, but the formula in the new worksheet is not.

Case 2: Defined Names Are Changed

To see an example of this behavior, follow these steps:

  1. Quit all running programs.
  2. In the Finder, select the icon for your hard disk. Click the title once. Type Freds Disk and press RETURN.
  3. Start Microsoft Excel. Note that the program may take longer than usual to start; this is because Microsoft Excel must re-register itself after you rename the hard disk.
  4. In the new workbook, select cell A1 in Sheet1. In the Name box (to the left of the formula bar), type test and press RETURN.
  5. On the File menu, click Save. Click the Desktop button. Change the file name to Workbook1 and click Save.
  6. Select Sheet2. On the Edit menu, click Move Or Copy Sheet. In the list box, click "(new book)." Select the Create A Copy check box, and click OK.

    A new workbook is created and activated.
  7. On the Insert menu, point to Name, and then click Define. Click "test" in the Names In Workbook list.
The Refers To box contains the following: 
   ='Freds DiskDes:ktop Folder:[Workbook1]Sheet1'!$A$1
Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbbug KB180617
©2002 Microsoft Corporation. All rights reserved.