Mar 21 2017 09:01 AM
Dear all,
I have a link in my excel (visible in data/edit links) but I cannot find in which cell this link exists. I have already tried to search for it with Ctrl F, but no result. If I try to relink, I get no error message, but the link is not replaced.
How can I find the cell that contains this link?
I'd rather not "break the link" to avoid wrong data in my file. It is quite a large calculation file, so not easy to look for it manually.
Many thanks for any input
with kind regards,
Wendy
Mar 21 2017 09:55 AM
Hi Wendy
Possible ways to look for links in workbooks
- Search for file types (e.g. xls, xlsx, xlsm).
- Name Manager
- Conditional formatting (>= XL2010)
- Data validation (>= XL2010)
Links:
http://blog.contextures.com/archives/2013/11/12/find-external-links-in-an-excel-file/
http://www.exceldashboardtemplates.com/how-to-fix-security-links-warnings-in-an-excel-spreadsheet/
http://nhsexcel.com/how-to-break-resistant-external-links-in-excel/
http://superuser.com/questions/915270/force-external-link-in-excel-to-break-if-the-file-cant-be-foun...
http://www.manville.org.uk/software/findlink.htm
Mar 25 2017 03:58 PM
I use Spreadsheet Compare 2016 from Office 2016 package. Quite often (but not always) it helps
- save file with another name
- break links in it
- compare with above tool initial and latest files
it shows the difference cell by cell
Apr 07 2017 12:26 PM
Hi
I've experience of such when there are name range setting contain reference from other file. Use 'Defined Names' functions under 'Formulas' tab and check if there are range name addressed from other files.
Regards
Claudia
Apr 11 2017 04:40 AM
This utility that @Detlef Lewin linked to is very useful
http://www.manville.org.uk/software/findlink.htm
Oct 10 2018 11:26 AM
This was the issue for me and the named ranges seem to have accumulate and persist from years old work.
Jul 27 2019 11:06 AM
@Wyn Hopkins thank you very much for referring back to the specific link by the other user that worked for you. It worked perfectly for me and reduced my file size and time to open, drastically.
It worked fine on my files that were listed under Data/Edit Links. I had other links or references that were hidden and the only way the add-in would delete them is if when opening the workbook, I had to ask Excel to try to open each file since it would prompt me as to whether I wanted to continue with these links or not. I could hit cancel on each one but that allowed me to record the file names from the pop-up so that I could then tell the add-in to delete them. They didn't show up for me any other way. It took a little time to do this but it's worth it since now when I open, no more links and they open much much much faster and the file size is greatly reduced.
The only problem I have is that sometimes after using the add-in's capability successfully on multiple files, even after I close and reopen Excel, I have to add the add-in back in by retrieving it from my folder. I could see the add-in in the add-in list in Excel and it is check-marked, but in order to get it to reappear on the top menu in Excel, I had to manually retrieve it again. It's not a problem, just strange.
Thanks again!
Mar 30 2021 03:53 PM
Aug 20 2021 09:43 AM
Nov 23 2021 02:31 PM
This may have been covered by someone else but I didn't see it covered so I'll mention:
I had a ghost link that turned out to be a macro from an old workbook. I must have copied over an object from that old workbook (linked to run a macro within the old workbook). In the new workbook it was trying to find the macro from the old workbook (but the old workbook had been subsequently moved). To correct the issue I simply changed the macro linked to that object to a macro that actually existed in the new workbook.
May 03 2022 08:33 AM
Jul 25 2022 01:40 PM
I had multiple phantom links and none of them showed up in the Find and Replace dialog box. I was able to get rid of some of the phantom links with Name Manager, but I figured out that Name Manager does not show names which are on hidden sheets. So, unhide all sheets in your workbook, then go to Name Manager and delete all bad references. Then re-hide the sheets you want hidden. Also, you may have to check if your workbook has Very Hidden sheets (yes, that is a real thing which is different from Hidden sheets) and unhide these too, otherwise they will not show up in Name Manager.
Hope this saves someone else some time.
Sep 12 2022 06:35 PM
Sep 12 2022 08:00 PM
See explanation of "very hidden" links below:
https://www.ablebits.com/office-addins-blog/2017/12/20/very-hidden-sheets-excel/
I do not know the answer to your second question.
Sep 23 2022 09:50 AM
@Wyn Hopkins the Add-in worked fine for my Excel 2010 spreadsheet. I couldn't open it to use once only. Had to install as an Add-in. Thank yall for the help specially to the author @Detlef Lewin.
Jul 04 2023 12:52 PM
@Brock1895 Exactly what I was looking for thank you!!!! Worked like a charm.
Nov 10 2023 09:34 AM - edited Nov 10 2023 09:38 AM
I had ghost links from two worksheets I copied over from an older workbook to my current one. I could not find them even after:
1. Break Links or Change Source from the Edit Links command in the ribbon
2. Checking Name Manager for ranges linked to outside workbooks
3. Removing all Conditional Formatting (this was in a spare testing copy)
4. Removing all Data Validation
5. Removing all my custom Format Styles
6. Did a Copy / Paste-Value for the two worksheets, essentially converting all formulas to hard-coded values
7. The links appeared only when I brought in the two worksheets. So there are no hidden worksheets in my current workbook that might hold these hidden links.
8. I cannot use outside utilities such as File Manager (same name as the Excel feature) that can look for hidden range names.
My final solution, which worked, is to run this code which I found in some forum which removes any hidden range names, and so I finally got the links to go away:
*********
Sub Remove_Hidden_Names()
Dim xName As Name
Dim k As Integer
Dim x As Integer
x = 0
On Error Resume Next
For Each xName In ThisWorkbook.Names
If xName.Visible = False Then
xName.Delete
x = x + 1
End If
Next xName
MsgBox x & " hidden names have been deleted.", vbInformation + vbOKOnly, "Hidden names deleted"
End Sub
Jan 31 2024 08:28 PM
Just to add one more thing to the list of possibilities, I copied a worksheet with a table to another book, and did a global replace to remove references to the original book in the table formulas, but there was still a link. I found two ways to remove the link.
Even though there were multiple columns with similar formulas, only one column retained the link, possibly because it contained 9 levels of nested XLOOKUPs.