XL: "Reference is Not Valid" Error with Lotus 1-2-3 Chart

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
• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel, when you open a Lotus 1-2-3 file that contains a chart, you receive the following error message when you activate the chart sheet or the sheet that contains the embedded chart:

   Reference is not valid. Reference for categories or values must be a
   single row or column.

CAUSE

This error appears when the y values for a data series reference more than one row or column of cells.

In Lotus 1-2-3, you can create a chart with a category or value axis that references more than one row or column of cells. However, this is not a valid reference in Microsoft Excel. When you open a Lotus 1-2-3 file that contains a chart with a value reference (y values) to more than one row or column, the reference is not modified, and you receive the error message above when you activate or edit the chart.

Note that in Microsoft Excel version 4.0, when you open a Lotus 1-2-3 file that contains a chart with a value reference to more than one row or column, that reference is modified to contain one row or column of data (the first row or column in the series). The reference is then valid in Microsoft Excel, and you do not receive an error message when you activate or edit the chart.

WORKAROUNDS

To avoid the error message when you open a Lotus 1-2-3 file that contains a chart, use the following steps to edit the affected data series so that the value reference contains only one row or column of data:

1. Select the affected chart series.

2. From the Format menu, choose Selected Data Series.

3. Select the Name And Values tab, and enter a correct range (one row or column of cells) in the Y Values box.

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.

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