Works 3.0 File Saved in Excel Format Returns #VALUE!

ID: q112447

The information in this article applies to:

Microsoft Works for Windows, version 3.0
Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0

SYMPTOMS

In Microsoft Works 3.0, if you save a spreadsheet in the Excel 4.0/5.0 SS format, some of the formulas will not be recalculated correctly in Microsoft Excel version 4.0, 4.0a, or 5.0. If a cell referenced by a formula is changed, then the formula will return the "#VALUE!" error value.

CAUSE

This problem occurs because of the way Works 3.0 saves spreadsheet files in the Microsoft Excel format.

RESOLUTION

Works 3.0 Users

To calculate formulas correctly in Microsoft Excel for a particular file, do the following:

In Microsoft Excel, open the file that you saved in the Excel 4.0/5.0 SS format.

Select the entire spreadsheet by pressing CTRL+SHIFT+SPACEBAR.

From the Formula menu, choose Replace.

Place an equal sign (=) in both the Find What and the Replace With boxes.

Choose Replace All.

From the File menu, choose Save.

The formulas will now recalculate correctly. Note that if you save the file in Excel 4.0/5.0 SS format again in Works 3.0, you will need to complete the above steps again.

STATUS

Microsoft has confirmed this to be a problem in Works version 3.0 for Windows. This problem was corrected in Works version 3.0a for Windows.

MORE INFORMATION

Steps to Reproduce Problem

In a new Microsoft Works spreadsheet, enter the number 1 in A1, 2 in B1, and the formula =A1+B1 in C1.

Save the file as TEST.XLS and choose Excel 4.0/5.0 SS from the Save File As Type box.

Open the file in Microsoft Excel version 4.0 or 5.0. Note that the values are correct in all three cells.

Change the number in A1 from 1 to 2.

Cell C1 now displays the #VALUE! error value instead of the number 4.

REFERENCES

For more information about saving Works 3.0 spreadsheets in other formats, refer to "Sharing files with other applications" on pages 50 and 51 of the "Microsoft Works for Windows User's Guide" for version 3.0.

For additional information about updating the values returned in Microsoft Excel, please see the following article(s) in the Microsoft Knowledge Base:

Q118936 Formulas in Imported Works File Not Calculated Correctly

For more information about importing spreadsheets from Microsoft Works to Microsoft Excel, query in the Microsoft Knowledge Base on the following keywords

cannot and open and W_Works and 3.0 and XL5

Additional query words: 3.00 3.00a 4.00 4.00a 5.00 ss interop converter conversion calculating incorrectly incorrect

Keywords : kbinterop
Version : WINDOWS:3.0,4.0,4.0a,5.0
Platform : WINDOWS
Issue type : kbbug