Find all formulae using defined names

[BigC]

I've been asked to review & clean up a workbook containing 6 sheets used to calculate labour charge out rates for a service provider to the mining industry (= many calculations for various resource types with multiple elements making up each rate)

The 1st major problem that I saw was 500+ defined names, almost all of which refer to other workbooks, and some that include #REF errors in their "Refers To" definition. (Funny thing is, when I open the workbook there is no dialogue box asking to Update Links?)

Is there a practical (& relatively quick) way to search and identify all formulae which use any of the defined names? (I'm thinking that VBA could be used to search each formula on the active sheet for any of the values (defined names) in a specified range, and either record the cell address of the target cell in another (output) range, or format the target cell with red fill/background? Any help with the code for such a routine would be greatly appreciated.)

Thanks in advance.

 

[mikerickson]

One quick and dirty way would be to delete some Names and then use Find to find all the #NAME? errors.

As I understand the situation, you have a workbook ("MessyBook.xls").
In that book are over 500 Names, some of which evaluate to an error.
Many of those 500 names refer to ranges in other workbooks.
You want to identify which cells in "MessyBook.xls" contain formulas that use named ranges.

1) Do you want to input a Name and get the cells that use that Name
or do you want the routine to return all cells that use any Names?

2) Do any of the Names refer to non-Ranges? e.g. Name:myDelimiter RefersTo:=","

3) Would a routine that deleted any Names that evaluate to an error be of use to you? (This could be restricted to those names that evaluate to a #REF error.) Or is there a concern that some cells may have formulas that refer to bad names and removing those bad names....

 

[mikerickson]

I just thought of a way to find all the cells that use myNamedRange, use Find, looking in formulas, with the search term myNamedRange. It will also pick up those cells that contain the explicit string "myNamedRange", but I don't think there will be too many of those.

 

[BigC]

Hi Mike

Thanks for the response.

Your understanding is correct.

Part 2 of Option 1 is my preference (i.e. a routine to return all cells that use any Names) - as there are 500 names I don't want to manually test each one. Fortunately (or unfortunately depending on how one looks at it) I don't think many of the formulae actually use these names - I think they've just legacy objects as a result of a multiple users progressively copying someone else's workbook but not tidying it up by deleting superfluous names and other crap >> layer upon layer upon layer like a Sarah Lea chessecake!)

Only one Names refer to non-Ranges - curiously to a constant with the value of "1".

I already have a routine that will delete all names in a specified range - so what I'm trying to do now is identify what names should go on that hit list (but I'd rather do it in a logical way than just fire the shotgun and hope that I don't hit something I want to keep!)

Thanks