I got a workbook from one of our accountants.

I ran across your blog trying to hunt down some phantom links, great resource. With persistence you will find the link(s), maybe with the above suggestions, or from your own ideas or suggestions by others. Good luck ! The above tactics are by no means a comprehensive list. If Left(cell.Formula, 1) = '=' And InStr(cell.Formula, '[') > 1 Then cell.Value = cell.Value VarLink = ActiveWorkbook.LinkSources(xlExcelLinks)ĪctiveWorkbook.ChangeLink Name:=varLink(intCounter), newname:=ThisWorkbook.Nameįor Each cell In activeworksheet.UsedRange Take a look at the chart series and the pivottable source ranges.ĭim varLink As Variant, intCounter As Integer If you have pivot tables or charts, they may contain source data that is derived from outside workbooks and hence being the links culprit. Save again and then delete the link you created. Now go to Edit > Links (version 2003) or the Name Manager (version 2007 or 2010), click the Edit button, and use the “Change Source” to refer the link to the new workbook. Open a new workbook, create a link to it, and save that workbook. In version 2007 or 2010, from the Ribbon click the Formulas tab, and in the Defined Names section click Name Manager. In Excel version 2003 or before, from the worksheet menu click Insert > Name > Define. Look closely at your list of defined names, not just in the name box but in the Define Name dialog, and make sure you do not have any named ranges refering to an outside Workbook. The idea is to look through all sheets for formulas referencing external workbooks, so be sure to unhide any hidden sheets with this tactic. Right-click on any sheet tab, left-click Select All Sheets, press Ctrl+F and in the Find what field enter Here are some tactics in no particular order for searching and destroying phantom links: So, how do you find a link when you have looked throughout your workbook and cannot locate what Excel says is there? In my 20 years working with Excel, I have not yet seen a single case when this message appeared and there was not a link Excel is very good at recognizing an existing link. It’s a common problem because some links can be difficult to find. If you’ve ever wondered why this message pops up after you are sure you’ve deleted all your workbook’s links, well, join the club. Tom’s Tutorials For Excel: Finding and Deleting Phantom Links

Finding hidden characters in excel 2013