XL2000: Invalid External Reference Error When Creating Chart (259625)

SYMPTOMS

When you click Finish or Next in step 2 of the Chart Wizard, you may receive the following error message:

Your formula contains an invalid external reference to a worksheet.

Verify that the path, workbook, and range name or cell reference are correct, and try again.

CAUSE

This behavior occurs when the reference in the Data Range box of the Chart Wizard is truncated, because it exceeds a length of approximately 235 characters.

Whether or not you receive the error message depends on how the reference is truncated. For example, if the reference "Sheet1!A10" is truncated to "Sheet1!A1", no error message appears because A1 is a valid reference, but the chart plots the incorrect cell value. (This behavior is a limitation of the Refedit control that the Data Range box uses.) However, if the reference is truncated to an invalid reference, such as "Sheet1!A" or "Shee", you receive the error message.

This behavior may occur when you create a chart with the Chart Wizard or modify an existing chart with the Chart Wizard.

WORKAROUND

To work around this behavior, reduce the length of the series. To do this, use any of the following methods:

Shorten the name of the worksheet used for the data range of the chart.
If your chart series refers to another workbook, also try shortening that workbook's file name.
If the data range for the chart is a noncontiguous selection, move all the data to a contiguous range in a new worksheet.

MORE INFORMATION

When this truncation occurs, the reference is always truncated in the Data Range dialog box in step 2 of the Chart Wizard, and in the Data Range box in the Source Data dialog box. The reference appears correctly as the third argument of the SERIES formula.