Defined name is not removed when you break a link in Excel (288853)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
This article was previously published under Q288853

SYMPTOMS

When you break a link containing a defined name that refers to an external source, the defined name is not removed.

CAUSE

This behavior is designed to prevent the accidental deletion of defined names that may be in use in various places in the file. When a defined name is deleted, any formulas that reference that defined name become #Ref! errors in the cells and must be re-entered to correct the behavior.

WORKAROUND

To work around this behavior, remove defined names that are being used by external links. To do so, follow these steps:
  1. Look through the list of defined names to identify the defined name that you want to remove. To do this, on the Insert menu, point to Name, and then click Define. Identify the defined name that you want to remove in the Names in workbook list. After you have the correct defined name, click Cancel.
  2. On the Edit menu, click Find, search for all the instances of the defined name that are in use in your file, and then remove them from use. Make sure that the Look In dropdown box in the Find dialog box says Formulas. You can remove the names from use by replacing the defined name with a value in the formula, or by removing the formula and replacing all the contents of the cell with a value.
  3. On the Insert menu, point to Name, and then click Define.
  4. In the Names in workbook list, click the name that you want to remove.
  5. Click Delete, and then click OK.

MORE INFORMATION

Microsoft Excel allows you to create a defined name to represent a cell, range of cells, a formula or a constant value in the current or in another workbook. For example, if a range in another workbook contains sales figures, you can name the range CurrentSales, and then use the name CurrentSales in your workbook, instead of referring to the range directly.

Note If you have a defined name in your workbook that refers to an external source, and that defined name is not used anywhere in the workbook, that link will not show up in the Edit Links dialog box.

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

837238 The link source for a defined name is not available in Excel 2002 and later


When you break an external link that is based on a defined name, all formulas and references based on that link are replaced by their current values. However, the link itself remains intact and you can continue to use it in future references and formulas. To completely delete the link, you must delete the defined name as outlined in the "Workaround" section.

REFERENCES

For more information about how to name cells, click Microsoft Excel Help on the Help menu, type Name Cells in a Workbook in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about breaking links, click Microsoft Excel Help on the Help menu, type Break Links in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about creating links to cells, workbooks and programs, click Microsoft Excel Help on the Help menu, type Link To Another Cell in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:9/23/2004
Keywords:kbprb KB288853
©2004 Microsoft Corporation. All rights reserved.