Find all formulae using defined names

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,206
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....
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,206
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

Well-known Member
Joined
Aug 4, 2002
Messages
851
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".:confused:

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!:eek:)

Thanks
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,742
Messages
5,833,430
Members
430,209
Latest member
addms

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top