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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mikerickson

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,606
Messages
5,512,390
Members
408,889
Latest member
2vbr

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top