Named ranges sourcing from another workbook

[hokirob]

I inherited a great Excel workbook solution with numerous names & macros in it. One frustration is if I create a new workbook for a new client or if I try to merge one worksheet tab into a different book.


Specifics: I have a named range "acct1" from a tab in Alex's workbook and it works fine. A different worksheet inside Alex's file references this named range with a formula like this: =if(acct1="","",acct1) so it's not fancy.


When I move the worksheet from Alex's working over to a new workbook for Betty, I want the formula to look at and reference the "acct1" named range in Betty's workbook. Where I'm stuck is that it's not -- it's looking back at the original in Alex's workbook and that value. To be specific, the "acct1" range in Betty's workbook may have the value of $100 -- but the other worksheet that was just imported is blank (because the "acct1" range on Alex's workbook is blank!)


Is it possible that when I copy worksheets over, they are keeping references to old data files? Maybe a way to break the link so that all my named ranges on Betty's worksheet look at and only reference her specific data fields?


I've seen a lot of similar topics but haven't exactly found it yet, so I'm hoping my post might generate a few ideas. Thanks to all.

 

[Luke M]

You are correct, the link is designed to still refer to the old workbook. However, it's fairly easy to fix. In 2003, goto Edit - Links. In 2007+, goto Data ribbon, then Edit Links under the connections block. This should display the links to the old workbook. Click on change Source, and change it to the active (correct) workbook. Ok out, done!

 

[hokirob]

Awesome, that seems to be it. Go figure it was linked to three other workbooks!! I'll have to sanity check some of the former ones as well I figure to ensure integrity. Much appreciated, Luke.