XL5: Formulas Incorrect When File Saved in SYLK Format

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel version 5.0 for Windows, if you save a file in the SYMBOLIC LINK (SYLK) file format, close the file, and then open the file again, groups of similar formulas may be converted into a single array formula that displays incorrect results.

The severity of this problem depends on the number of shared formulas that are contained in the file. If there are many shared formulas in the file, the file may appear to have been drastically altered; however, you should not lose data because of this problem.

WORKAROUNDS

To work around this problem, do either of the following:

• If your file contains many potential shared formulas (for example, columns or rows of cells that contain the same basic formula), do not save the files in the SYLK file format. Use an alternate format, such as the Microsoft Excel Workbook format or the Microsoft Excel 4.0 Worksheet format.

  -or-

• If you have not yet saved the file in the SYLK format, you can convert your formulas to text strings (text strings are not affected by this problem). To prevent formulas from being altered, follow this procedure:

  1. Activate the worksheet that you want to save in the SYLK format.

  2. From the Edit menu, choose Replace.

  3. In the Replace dialog box, in the Find What box, type an equal sign

           =
  
        and in the Replace With box, type an apostrophe and an equal sign:
  
           '=
  
     4. Choose the Replace All button.
  
     The formulas, rather than the results of the formulas, will be displayed
     in your worksheet.
  
     5. Save the file in the SYLK format.
  
     When you reopen the SYLK file, do the following to restore your
     formulas:
  
     1. From the Edit menu, choose Replace.
  
     2. In the Replace dialog box, in the Find What box, type an apostrophe
        and an equal sign
  
           '=
  
        and in the Replace With box, type an equal sign:
  
           =
  
     3. Choose the Replace All button. If you receive the error message
        "Cannot find matching data to replace," choose the OK button.
  
     4. From the Edit menu, again choose Replace.
  
     5. In the Replace dialog box, in the Find What box, type an apostrophe
        and an equal sign:
  
           '=
  
        and in the Replace With box, type an equal sign:
  
           =
  
     6. Choose the Replace All button.
  
     The formulas in the file should now be restored.
  
  

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

MORE INFORMATION

In Microsoft Excel version 5.0, if you save a file in the SYLK format, shared formulas in the file may be converted to array formulas. When the file is opened again, these array formulas are not properly converted back to individual formulas.

You should not lose data because of this problem. Cells that do not contain formulas are not affected by this problem. Only cells containing formulas are affected.

For more information about the "shared formulas" method used in Microsoft Excel version 5.0, query on the following words in the Microsoft Knowledge Base:

   shared and formulas and enhancement and time