Article ID: 924458
Article Last Modified on 10/15/2007
APPLIES TO
- Microsoft Office Excel 2007
For a Microsoft Office Excel 2003 and a Microsoft Excel 2002 version of this article, see 288853.
SYMPTOMS
In Microsoft Office Excel 2007, you break a link that contains a defined name. This defined name refers to an external source. When you do this, the defined name is not deleted.
CAUSE
This behavior is designed to prevent you from accidentally deleting defined names that may be used in various locations in the Excel 2007 workbook. When a defined name is deleted, any formulas that reference that defined name become #Ref! errors in the workbook cells. These formulas must then be re-entered.
WORKAROUND
To work around this behavior, delete defined names that are being used by external links.
To do this, follow these steps:
- Examine the list of defined names to identify the defined name that you want to delete. To identify the defined name, follow these steps:
- Click the Formulas tab, and then click Name Manager in the Defined Names group.
- In the list in the Name Manager dialog box, identify the defined name that you want to delete.
- Click Close to close the Name Manager dialog box.
- Click the Home tab.
- In the Editing group, click Find and Select, and then click Find.
Search for all instances of the defined name that are being used in your workbook, and then remove them from use. Make sure that Formulas is selected in the Look in box on the Find tab.
Note You can remove the defined names from use by replacing the defined name with a value in the formula. Alternatively, delete the formula, and then replace all the contents of the cell with a value. - Click the Formulas tab, and then click Name Manager in the Defined Names group.
- In the list in the Name Manager dialog box, select the defined name that you want to delete, and then click Delete.
- Click Close to close the Name Manager dialog box.
MORE INFORMATION
Excel 2007 lets you create a defined name to represent a cell, a range of cells, a formula, or a constant value in the current workbook 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 You may have a defined name in your workbook that refers to an external source, and that defined name may not be used anywhere in the workbook. In this case, that link will not appear in the Workbook Connections dialog box.
When you break an external link that is based on a defined name, all formulas and references that are based on that link are replaced by their current values. However, the link itself remains intact. You can continue to use the link in future references and formulas. To completely delete the link, you must delete the defined name as outlined in the "Workaround" section.
Additional query words: excel2007 excel2k7 excel12 xl2007 xl2k7 xl12
Keywords: kbtshoot kbprb kbexpertisebeginner KB924458
