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

• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, 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 within 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 its 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 prevent these problems from occurring, verify that defined names in the workbook do not refer to entire columns of cells or 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 97, worksheets may contain up to 65,536 rows. 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 by Microsoft Excel 97. In particular, defined names may be affected by the process.

In Microsoft Excel 97, when you open a Lotus 1-2-3 file that you saved from Microsoft Excel 97, defined names are affected in the following ways:

• If a defined name refers to a cell (or cells) after row 8,192, the row numbers of the cells are decremented in units of 8,192 rows 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
  
  

• If a defined name refers to an entire column (or columns), or to a cell (or cells) in row 65,536, the defined name is changed so that it refers to the same row numbers, but the row numbers are in columns A through H.

  Examples of columns that are changed are listed in the following table.

        Range Originally Used          Range Used After Reopening
        ---------------------------------------------------------
  
        =Sheet1!$A:$A                  =Sheet1!$A:$H
        =Sheet1!$K:$X                  =Sheet1!$A:$H
        =Sheet1!$M$65536               =Sheet1!$A$65536:$H$65536
  
  

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.