XL: Linked Cell in WK3/WK4 File Displays #NAME? or Zero

ID: Q123267

The information in this article applies to:

Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, versions 5.0, 5.0c

SYMPTOMS

In Microsoft Excel, when you open a Lotus 1-2-3 WK3 or WK4 file that contains a link to a name in another Lotus 1-2-3 WK3 or WK4 file, one of the following occurs if the name is defined on a worksheet other than the first worksheet in the Lotus 1-2-3 file:

Lotus WK3 File

When you open the file, the linked cell displays the value zero.

Lotus WK4 File

When you open the file, you receive the following error message:

This document contains links. You can re-establish them with the Edit Links command."

When you choose OK, the linked cell displays the #NAME? error value.

CAUSE

This behavior occurs when you open a WK3 or WK4 file that references a name defined in another file, if the referenced file is not open, because a reference to a name defined on a worksheet other than the first worksheet in the file is not converted. For example, if you open a WK3 or WK4 file that contains the following formula


   +<<TEST.WK4>>rangename

where rangename refers to B:A1 (B is the second worksheet in the file), the cell that contains this formula displays the value zero (WK3) or the #NAME? error value (WK4).

In this case, choosing the Update Now button in the Links dialog box does not update the link.

WORKAROUND

To work around this behavior, use any of the following methods.

Method 1

In Lotus 1-2-3, when you reference a range of cells in another file, use the cell range reference, such as +<<TEST.WK4>>B:A1, instead of a defined name.

Method 2

In Microsoft Excel, open the referenced file either before or after you open the file that contains the reference.

Method 3

To save both the file that contains the reference, and the referenced file to the Microsoft Excel Workbook format, follow these steps:

Open the file that contains the reference.

Choose Links from the Edit menu.

Choose the Open Source button.

Switch to the file that contains the reference. Save the file to the Microsoft Excel Workbook format.

Switch to the referenced file. Save the file to the Microsoft Excel Workbook format.

This problem does not occur when you save the files to the Microsoft Excel Workbook format.

MORE INFORMATION

For additional information, please see the following articles in the Microsoft Knowledge Base:

Q122890 XL5: Exporting Lotus 1-2-3 Linked Files to Excel

Q123262 XL5: External Link in WK4 File Displays #REF! Error Value

To open Lotus 1-2-3 WK4 files in Microsoft Excel, you must obtain the Lotus 1-2-3 WK4 File Converter. The Lotus 1-2-3 WK4 File Converter is available in application note WE1130 "Lotus 1-2-3 WK4 File Converter."

For more information about obtaining the Lotus 1-2-3 WK4 File Converter, please see the following article in the Microsoft Knowledge Base:

Q122583 XL5: Lotus 1-2-3 Release 4 for Windows File Converter

Lotus 1-2-3 is manufactured by Lotus Corporation, a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.

REFERENCES

Lotus 1-2-3 WK4 File Converter README.TXT

For an Overview Of Linking Microsoft Excel Workbooks, choose the Search button in Help and type:

linking, workbooks

Additional query words: 5.00a 5.00c 7.00a

Keywords : xlwin
Version : 5.00 5.00c 7.00 7.00a
Platform : WINDOWS
Issue type :