More name questions

[cecil]

How do I determine if a name is used in a formula?

I did download Jan Karel Pieterse, but that does not help me. I have 85 workbooks in this model. I need to know if the name is referenced from another workbook in the model.

The direction I headed was to do a find. Following is a piece of that code. It works for some cases. If I am headed the right direction, I will continue to determine what patterns I need to look for. The case I found using Jan Karel's tool, which I had not considered, is a formula that uses the intersection of two names.

Code: Select all

               
'after setting the "lookfor" variable = the pattern I need to find, do the following.
On Error Resume Next
Set cFoundIn = sht2.Cells.Find(What:=lookfor, After:=sht2.Cells(1, 1), LookIn:=xlFormulas, _
       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)
                        
if Err.Number = 91 Then
      bFound = False
Else
       bFound = True
end if

Here is the bottom line. Someone back in Excel 95 era created a complicated financial model with 85 workbooks, over 600 names per book and over 2500 formula per book. These roll up from department to division to corp. In their infinate wisdom, they used the same name for sheet and book scoped names. I need to find and rename one or the other without breaking the model.

I am making progress on this mess!

[Jan Karel Pieterse]

Now that you have downloaded me, I'm always there to help!

Try downloading my Flexfind utility: http://www.jkp-ads.com/officemarketplaceff-en.asp

You could also have a look at some sample code I provided for bulk-renaming many range names using my Name Manager (see its manual)

[cecil]

My question remains
How do I determine if a name is used in a formula? Am I taking the right approach by using the "Find"

[Jan Karel Pieterse]

No, Find is not sufficient, it can return false positives:
Looking for "Name" will also give you:
=Name1
=AnotherName
=IF(A1="","Please enter your Name","")

Also, find will not tell you if a name has been used in a validation formula or a conditional formatting formula or in controls and etcetera.
My Name Manager does cater for all these variations.

[Jan Karel Pieterse]

I have a question on your workbook set:
Are workbooks using named ranges which are in one of the other workbooks?

[cecil]

Yes. For example, The workbooks for the teams have range names like Apr and Apr_totals. The rollup workbook will reference the team workbook named Apr_totals. Therefore I need to loop every workbook higher in the archive model to see if the name is utilized.

[Jan Karel Pieterse]

Well, Name Manager isn't up to that job. I could hepl you by developing a special version which can do this, but I can't afford doing that for free I'm afraid.
You could use my FlexFind I mentioned earlier. It searches all open workbooks and has a list option which creates a listing of all found instances of your string. Such a list may be easier to check for true hits than doing so manually?