View Full Version : Scope of Two Defined Identical Names

06-11-2006, 08:31 PM
Consider the following situation:
I created two workbooks, ?A.xls? and ?B.xls?.
In each workbook, I define a cell as having the name ?Rsrvd?.
In workbook ?A.xls?, the name ?Rsrvd? is associated with cell A1 on Sheet1.
In workbook ?B.xls?, the name ?Rsrvd? is associated with cell A2 on its Sheet1.

Now I copy Sheet1 from ?B.xls? to ?A.xls?. The copied sheet is automatically renamed to be Sheet1 (2) in ?A.xls?.
In ?A.xls? when Sheet1 is active, I look at the list of defined names and I see ?Rsrvd?, and in the ?Refers to? window I see ?=Sheet1!$A$1?.
In ?A.xls? when Sheet1 (2) is active, I look at the list of defined names and I also see ?Rsrvd?, except on the right side of the defined names list I see ?Sheet (2)?, and in the ?Refers to? window I see ?=Sheet1 (2)!$A$2?.

If I activate a third sheet in ?A.xls?, then run a macro that has the statement Range(?Rsrvd?) = ?Food?, I find that cell A1 on sheet Sheet1 (the original sheet in that book to have the name ?Rsrvd?) now contains the value ?Food?.
If I activate sheet Sheet1 (2) then run the same macro, I find that cell A2 now has the value ?Food? in cell A2.

This suggests to me that a defined name that is inherited by a workbook as a consequence of receiving a sheet copied from another workbook will be recognized only when the copied sheet is active. If any other sheet is active, then the original version of the defined name will be recognized.

Just to make sure I understood all this, I deleted the original ?Rsrvd? in ?A.xls?, so that now the ONLY version of name ?Rsrvd? is the one associated with the copied sheet. Since there would be no ambiguity, I reasoned that now it wouldn?t matter which sheet is active when I run my macro. Inserting a value into the named cell should wind up in cell A2. To my surprise, that didn?t happen. When any sheet other than the copied sheet was active, I got a ?not recognized? error.

The bottom line apparently is that any defined name inherited by a workbook as a result of having a sheet from another workbook copied into it will NOT be available to any worksheet other than the copied sheet.

But there is still another case I wondered about. Suppose I defined the name ?Rsrvd? so that I used a formula instead of naming a cell. I used the following definition: =Today(). I noticed that the definition in the ?Refers to? window did NOT have a sheet name as a prefix. All I saw was ?=TODAY()?.

So I repeated the tests above, and I found that, if I deleted name ?Rsrvd?, it was deleted for ALL sheets. It didn?t matter which one originally had the definition. When I used it, I got a #NAME? error.

So my conclusions must be extended to say that, if the defined name is for a formula, then any sheet can use it. Well, not quite. The restriction is that the formula cannot include a reference to a specific cell. For example, if I made the formula ?=Today() + A1?, then everything reverts to the rules described at the beginning, which isn?t exactly surprising.

Since I didn?t see this written up anywhere, my question to the forum is: is this the way it works, or have I missed somethiing?

06-14-2006, 01:14 PM
Too wordy, huh? Well, let me try to reduce it to this:
If I copy a worksheet from one workbook to another, then any names defined for the copied worksheet will also be copied to the target workbook, thereby adding to its defined name list.

Now the target workbook has the same identical defined name in its defined name list twice, but possibly with two different definitions. From my tests it appears that:

1) The inherited defined name cannot be referenced by any sheet other than the copied sheet IF the name definition contains a reference to a cell.

2) If the name definition contains a formula without cell references (e.g., = TODAY() + 1), then all sheets can reference the inherited name.

This situation can cause a lot of confusion sometimes if a programmer KNOWS that the copied sheet brought a defined name with it, then tries to use that name on another sheet.

I just stumbled onto this factoid, and I wondered if I have a correct understanding of the scope of defined names?

06-14-2006, 01:52 PM

Surely all you are experiencing is the difference between local (worksheet level) and global (workbook level) names.