Wednesday, April 3, 2013

Excel: Finding a Broken Link

I have several Excel files that have been edited by others over the years.  Whenever I open some of these spreadsheets, I get the broken link window.  I can click on Edit Links, but all that gives me is a window saying “Error: Source no found” for the broken link.

I tried going to the Formulas tab>Show Formulas.  This turns all the formulas into text and spreads out the column width so I can see the formulas, but I still didn't find the broken links.

I googled “find broken links” and went to Microsoft’s community page.  Here a wonderful contributor Dave Peterson (thanks again, Dave) posted a link to an add-in that he had discovered:

I'd use Bill Manville's FindLink program:

I was hesitant about grabbing an unknown zip file, but everyone on the community board was raving about it, so I tried.  I had never looked at the Add-Ins tab, but now I had one called Find Links.  I clicked, entered a few words from the error message and it found the broken link.  Someone had put information on Sheet2 of the workbook.  I hadn’t thought to look there.  

(Quick tip:  If you are using more than one sheet, make sure you give them names other than Sheet1, Sheet2 and Sheet3.)

I deleted the year old information on Sheet2, and saved the spreadsheet.  When I reopen it, no more broken link message!

No comments:

Post a Comment