Solved

Updating Links

Posted on 2011-09-19
8
259 Views
Last Modified: 2012-05-12
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
Comment
Question by:Bright01
  • 4
  • 2
  • 2
8 Comments
 
LVL 5

Expert Comment

by:slycoder
ID: 36561892
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
 
LVL 5

Expert Comment

by:slycoder
ID: 36561931
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
 

Author Comment

by:Bright01
ID: 36562359
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
 
LVL 41

Accepted Solution

by:
dlmille earned 250 total points
ID: 36562434
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Bright01
ID: 36562694
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
 
LVL 41

Expert Comment

by:dlmille
ID: 36563305
Yes, you can delete ref errors.

Can you upload (is it sensitive info)?

Dave
0
 

Author Comment

by:Bright01
ID: 36564302
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
 

Author Closing Comment

by:Bright01
ID: 36564306
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now