XL98: Defined Names Saved Incorrectly in Lotus 1-2-3 Format

• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel 98 Macintosh Edition, when you save a file in the Lotus 1-2-3 WK4, WK3, or WK1 file format, the following problems may occur:

• When you reopen the file in Microsoft Excel, defined names in the workbook refer to incorrect ranges. However, the workbook functions correctly in other respects.
• When you open the file in Lotus 1-2-3, you receive the following error message:

        Error reading file
  

  Although you can open the file, much (or all) of the data is missing.

CAUSE

These problems occur when either of the following conditions is true:

• A defined name in the workbook refers to an entire column, or to multiple columns of cells.

  For example, the defined name refers to the following cells:

        =Sheet1!$A:$A
        =Sheet1!$D:$K
  

      -or-
  

• A defined name in the workbook refers to a cell (or cells) beyond row 8,192.

  For example, the defined name refers to the following cells:

        =Sheet1!$A$8195
        =Sheet1!$D$65536
  

WORKAROUND

To work around these problems, verify that defined names in the workbook do not refer to entire columns of cells or to cells beyond row 8,192 before you save the workbook in the Lotus 1-2-3 WK4, WK3, or WK1 format.

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

In Microsoft Excel 98 Macintosh Edition, worksheets may contain up to 65,536 rows. However, in the Lotus 1-2-3 WK4, WK3, and WK1 file formats, worksheets may contain up to 8,192 rows. Because of this difference, you may experience problems when you open files that are saved in a Lotus 1-2-3 file format from Microsoft Excel 98 Macintosh Edition. In particular, defined names may be affected by the process.

In Microsoft Excel 98 Macintosh Edition, when you reopen a Lotus 1-2-3 file that you have saved from Microsoft Excel 98 Macintosh Edition, defined names are affected as follows:

   If a defined name refers to a cell (or to cells) after row 8,192, the
   row numbers of the cells are decremented in units of 8,192 until the row
   numbers are less than or equal to 8,192.

   Examples of row numbers that are changed are listed in the following
   table.

      Original range used            Range used after reopening
      ---------------------------------------------------------

      =Sheet1!$A$1:$A$8195           =Sheet1!$A$1:$A$3
      =Sheet1!$A$8193:$A$8200        =Sheet1!$A$1:$A$8
      =Sheet1!$A$8190:$A$8195        =Sheet1!$A$3:$A$8190
      =Sheet1!$A:$A                  =Sheet1!$A$1:$A$8192
      =Sheet1!$K:$X                  =Sheet1!$K$1:$X$8192
      =Sheet1!$M$65536               =Sheet1!$M$8192

If you attempt to open a Lotus 1-2-3 WK4, WK3, or WK1 file that you saved with this kind of defined name in Lotus 1-2-3, you may receive the error message in this article. If this problem occurs, close the file without saving changes. You can then reopen the file in Microsoft Excel and correct the problem with the defined names.