Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

The link source for a defined name is not available in Excel


View products that this article applies to.

Symptoms

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

Note To locate the Edit Links dialog box in Excel 2003 and in earlier versions of Excel, click Links on the Edit menu.

To do this in Excel 2007, click Edit Links on the Data tab in the Connections group.

Note To determine whether you have a defined name or to add a defined name in Excel 2002 and in Excel 2003, point to Name on the Insert menu, and then click Define.

To do this in Excel 2007, click Define Name on the Formulas tab in the Defined Names group.

↑ Back to the top


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 because this is a design change.

↑ Back to the top


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.

↑ Back to the top


More information

If a reference that is in a formula or in 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 or the Edit Links option will not be available if the workbook does not contain any other link sources.

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

↑ Back to the top


References

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
288853 Defined name is not removed when you break a link in Excel

↑ Back to the top


Keywords: KB837238, kbprb, kbpending

↑ Back to the top

Article Info
Article ID : 837238
Revision : 5
Created on : 2/1/2007
Published on : 2/1/2007
Exists online : False
Views : 313