Range names issue

Topic

New Reply

I have 2 workbooks as follows

Book1 contains two named ranges. MyRange1 has a scope of sheet1 and MyRange1 has a scope of Workbook. Note: two distinct ranges, two different scopes, both have the same range name.
Book2 has links to book1’s MyRange1 at the workbook scope. See the formula’s in column B. =VLOOKUP(A2,book1.xlsm!MyRange1,2)

If I open book2, then open book1, The range with the workbook scope is missing and the link in Book2 is changed to look at the sheet scoped range name.

I have found a bit of discussion regarding this issue using Google, but I have not found a solution.
These workbooks likely were created using Excel 95. They worked well in Office 2003.

Reply | Quote

Replies

Cecil,

It’s bad coding practice to use 1 name to mean two different things. It’s just plain confusing , which is never good.
By simply changing the name of one of the ranges {the non referenced one} the problem is solved. What is your reason for wanting the two ranges to have the same name?

BTW: I would also advise using more descriptive range names, e.g. TeamTable1, TeamTable2, etc.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I don’t want two ranges to have the same name. Someone forgot to tell the accounting folks about this bad practice, therefore that is the way it is. We have a huge model, with speadsheet rolling up 4 layers. Bad programming practice or not, these models have been used for years, until we installed Office 2007.

The files I uploaded are simply a dumbed down model of the problem. By using reductionism, I can isolate the problem and prove it is repeatable.

The actual model uses descriptive range names.

Reply | Quote

Cecil,

I feel your pain! Been there done that!

That said now is the time to fix the situation before it gets worse! I worked 26 years for the good old USG where there was never time to do it right but always time to do it over and over and over.

Good luck! You’re gonna need those beers!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I’ll add  one comment to the global and local named range issue.  I frequently use the same name in multiple worksheets that are identical in design and data structure with no problems at all, but will never use the same name locally and globally.

I get the opposite results of what you did, but I opened Book1 first to make the names available to Book2 as soon as it opens.  The vlookup of Book2 results in in the display of the globally named range, not the Sheet1 range of Book1.

To link to the range name MyRange1 defined for Sheet1 of Book1, I changed the vlookup formula to =VLOOKUP(A2,[book1.xlsm]Sheet1!MyRange1,2).

As you know, you probably need to change your global name which is easily accomplished in the Name Manager to something that reflects the purpose of the name.

Reply | Quote

I am aware that if I change the order in which the workbooks are open, all works fine. However, I cannot ensure the end users follow that same pattern. I have ~90 workbooks in this model. It looks like we will have to put the time into finding all instances where the originator employed this bad programming practice and fix it. Obviously we will need to do some regression testing to ensure the model remains valid. I was hoping someone knew about a fix for this, especially since it worked fine in 4 previous versions of Excel.

Like I often say, I was looking for work applied here, well, I found it!

Reply | Quote

Have a look at Name Manager by Jan Karel Pieterse at http://www.jkp-ads.com.  There is a free version you can checkout that has many of the features you may want to use disabled, but you’ll at least be able to see what it is capable of doing for you.

Mark

Reply | Quote

There isn’t a paid for version of JKP’s Name Manager so there are no limitations on the free one.

Reply | Quote

Rory,

You are correct, however there are several features in Name Manager that are only made available once you purchase FastExcel.  I just checked and I have the latest release, 4.2 Build 621, and it still has these limitations. 

Mark

Reply | Quote

Ah, perhaps that’s new in 4.2 (which I believe Charles did a lot of work for). I don’t recall having seen that before.

Reply | Quote