The link source for a defined name is not available in Excel 2002 and Excel 2003 (837238)

SYMPTOMS

If you have a defined name in a Microsoft Excel 2002 or Microsoft Office Excel 2003 workbook that contains a link to another workbook, the link source may not be listed in the Edit Links dialog box (on the Edit menu, click Links).

Note To determine if you have a defined name or to add a defined name, on the Insert menu, point to Name, and then click Define.

CAUSE

This behavior occurs when the defined name that contains a link to another workbook is not used in your current workbook's calculation chain. This behavior is different from earlier versions of Excel as this is a design change.

WORKAROUND

To work around this behavior, add a reference to the defined name in your workbook if you know that your workbook contains a defined name that contains a link to another workbook.

For example, if you have a defined name of Test, type =Test in a cell of your worksheet, and then press ENTER.

MORE INFORMATION

If a reference that is in a formula or another defined name contains a link to another workbook that is not contained in the workbook, the link source for the defined name is not available in the Links dialog box.

You cannot use the Change Source feature (in the Edit Links dialog box) because the link source is not listed. Additionally, the Links option on the Edit menu will not be available if the workbook does not contain any other link sources.

This new behavior in Microsoft Excel was introduced to minimize the number of update link prompts that you may receive when your workbook is updated. As the defined name is not used in the workbook's calculation chain, it is not necessary to update the link.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

288853 Defined Name Not Removed When You Break a Link