Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    [attachment=90284:ExcelExamples.zip]
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbury, GA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.



  6. #6
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbury, GA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Have a look at Name Manager by Jan Karel Pieterse at 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


  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    There isn't a paid for version of JKP's Name Manager so there are no limitations on the free one.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbury, GA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •