• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Updating Links

I have copied a sheet from one workbook to another and I continue to get a "Update Links" each time I open the new WB.  Although I can see that the link takes me back to the old WB, and if I break the link, I then lose some functionality in the new sheet....... is there a way to find out where the link is in the code or WB so I can fix it and then break the link?

Thank you,

B.
0
Bright01
Asked:
Bright01
  • 4
  • 2
  • 2
1 Solution
 
slycoderCommented:
I think a simple way would be to search for "=+'["
without quotes:
=+'[

in formulas - this should jump you to each cell that has a sheet reference e.g.: =+'[myWorkbook.xls]Sheet1'!$E$6.

Thanks
0
 
slycoderCommented:
If this is really time-exhausting while you are making updates, you can shut them off temporarily with:

File - Options - Advanced Tab, "When calculating this workbook " section  

uncheck: Update links to other documents.



Thanks

0
 
Bright01Author Commented:
Slycoder,

Thanks for the quick follow up.  Is there a way to scan the entire Workbook?  I did a Find and included;

=+'[

But nothing came back. Do I have to do it for each sheet?  What about any Macro or Range Name References?

Thank you,

B.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dlmilleCommented:
That's a good tip, however I would suggest:

1. Selecting all TABS in the workbook
2. Searching (using Excel Find) for ".XLS" <- this would find any workbook, any version, as opposed to "=+'[" as the + may or may not exist in a workbook link reference
   
On the Excel FIND option:

Use the FIND ALL option, so all the places where this shows up is listed in the find dialog box
Ensure Match Case is not selected
Ensure Match Entire Cell Contents is not selected
Search in FORMULAS, as opposed to VALUES


See Find dialog image, below
 find .XLS3. Go to the Names Manager in the FORMULAS Ribbon, under DEFINED NAMES and review the names listed.  Links can also be embedded in Names of a workbook.

See Names Manager dialog image, below
 names managerCheers,

Dave
0
 
Bright01Author Commented:
Dave,

Thanks for the tip.  I did 1, 2 and 3 and still didn't match up with what the Link is showing me (from the Source where I copied it....but doesn't show up in any of the tabs or the Range Name Mgr.).  BTW; where I have Ref errors on the Range Name, may I assume those range names are irrelivant in the new model?  Anywhere else to look?  If not I'm going to manually force a non-update to the sheet.

Comments?

Thanks,

B.
0
 
dlmilleCommented:
Yes, you can delete ref errors.

Can you upload (is it sensitive info)?

Dave
0
 
Bright01Author Commented:
Dave,

I actually found the link...it was in a macro.  I'm not sure how you find errors in macros other then look througth each of them for references.

Thank you,

B.
0
 
Bright01Author Commented:
Thanks Dave,

Your tips were very good and forced me to look at the Macros which is where I found the reference.  

B.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now