Names | Remove #REF! names

The OAK4 | Names | Remove #REF! Names command builds a list of all the names in the active workbook that have the text #REF! in their definition. These names are displayed in a dialog and you can then delete all the #REF! names in a single step.

If you give a name to a cell which you later delete, the formulas which refer to these names will now display #REF! By looking under ‘Refers to’ in the Define Name dialog box, Insert | Name | Define, we are able to see what exactly has been deleted to cause the #REF! error:

•	if ‘Refers to’ displays something like “=#REF!$A$5”, it indicates that the cell disappeared because you deleted the whole sheet that it was on;

•	if ’Refers to’ displays something like “=Sheet1!#REF!”, it indicates that the worksheet is still there, but the cell has disappeared, most likely because you removed the row or column containing it.

Names with #REF! in their definitions should be removed from your model, as they refer to cells that are no longer in the workbook. At the very least, they clutter up the list of names, where they can’t do anything useful. At worst, they may actually still be being used in a formula, leading it to give results different from those intended.

The Remove #REF! Names command displays a list of all the names that have the string “#REF!” anywhere in their definition. If you would like to permanently remove all of these names from your workbook, simply select all names by using the 'Select All' checkbox, then click the ‘Remove Selected’ button. If you would only like to remove certain names, then select them as required and click the ‘Remove Selected’ button. Otherwise, press the ‘Cancel’ button.

•	check that there are no such broken names in your model;

•	remove any that are in the model with a single button;

•	if you prefer, go and check out the names identified in the list one by one and fix them individually.

If you didn't have OAK, the only ways to remove broken names from a worksheet are

•	to delete them manually one at a time using the Define name dialog;

•	to write a few lines of Visual Basic that will achieve the same thing.

Very exceptionally, a name can have the string “#REF!” in its definition intentionally; OAK will not know that and will include the name in the list to remove.

Removing #REF! names will likely result in any formulas which refer to the deleted names to return #NAME? errors as, now, the names that are referred to are undefined instead of referring to a cell or range that no longer exists. Further attention to is needed to remove these #NAME? errors.