SOLVED

Hidden External Reference

%3CLINGO-SUB%20id%3D%22lingo-sub-1285415%22%20slang%3D%22en-US%22%3EHidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1285415%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20think%20I'm%20pretty%20Excel%20savvy%20but%20this%20problem%20comes%20up%20every%20so%20often.%26nbsp%3B%20I%20have%20copied%20some%20info%20and%2For%20sheets%20from%20another%20workbook%20and%20now%20this%20new%20workbook%20keeps%20saying%20it%20can't%20re-connect%20with%20the%20previous%20workbook.%26nbsp%3B%20I%20have%20searched%20the%20entire%20workbook%20(including%20formulas)%2C%20the%20entire%20VBA%20space%2C%20the%20data%20model%20(empty)%2C%20existing%20connections%20(empty)%20and%20can't%20find%20this%20link%20anywhere.%26nbsp%3B%20I%20do%20the%20Inspect%20Document%20for%20personal%20information%20and%20it%20also%20finds%20an%20external%20link%2C%20but%20I%20can't.%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20tips%20or%20suggestions%3F%3C%2FP%3E%3CP%3Ethank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1285415%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1285715%22%20slang%3D%22en-US%22%3ERe%3A%20Hidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1285715%22%20slang%3D%22en-US%22%3EI've%20experienced%20the%20same%20phenomenon%2C%20fortunately%20only%20on%20one%20workbook...so%20I'm%20eagerly%20awaiting%20what%20others%20might%20offer%20as%20the%20reason%2C%20and%20even%20more%2C%20what%20the%20solution%20will%20be.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1286098%22%20slang%3D%22en-US%22%3ERe%3A%20Hidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1286098%22%20slang%3D%22en-US%22%3ETry%20looking%20at%20the%20Name%20Manager%20(Ctrl%2BF3).%20There%20might%20be%20defined%20names%20that%20refer%20to%20an%20external%20workbook.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1286305%22%20slang%3D%22en-US%22%3ERe%3A%20Hidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1286305%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%20excellent%20suggestion%2C%20but%20unfortunately%20I%20already%20checked%20there%20also.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1286883%22%20slang%3D%22en-US%22%3ERe%3A%20Hidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1286883%22%20slang%3D%22en-US%22%3E%3CP%3Enow%20I've%20checked%20all%20the%20objects%2C%20graphs%2C%20and%20conditional%20formatting.%26nbsp%3B%20I've%20done%20additional%20'find%20all'%20searches%20to%20no%20avail.%20%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20Excel%20be%20%22smart%22%20enough%20to%20tell%20me%20there%20is%20this%20missing%20link%20but%20not%20able%20to%20give%20me%20any%20hint%20as%20to%20where%20it%20is.%3C%2FP%3E%3CP%3EBTW%2C%20at%201%20point%20I%20was%20given%20the%20option%20to%20'break%20the%20link'%20and%20did%20that%20but%20it%20still%20warns%20me.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1290442%22%20slang%3D%22en-US%22%3ERe%3A%20Hidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1290442%22%20slang%3D%22en-US%22%3ESelect%20your%20preferred%20option%20in%20the%20Edit%20Links%20(Alt%2CA%2CK)%20dialog%20box%2C%20one%20of%20which%20is%20Break%20Links.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1290497%22%20slang%3D%22en-US%22%3ERe%3A%20Hidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1290497%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%20thank%20you%20again.%26nbsp%3B%20as%20I%20mentioned%20I%20already%20told%20Excel%20to%20break%20the%20link%20but%20it%20still%20warns%20me.%26nbsp%3B%20After%20I%20open%20the%20workbook%20I%20go%20to%20Alt%2C%20A%20(Data%20Tab)%20and%20'Edit%20Links'%20is%20grayed%20out.%3C%2FP%3E%3CP%3EI%20wish%20I%20could%20go%20into%20an%20editor%20and%20search%20the%20'raw'%20code%20behind%20the%20workbook%20to%20find%20the%20link%20location.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1290566%22%20slang%3D%22en-US%22%3ERe%3A%20Hidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1290566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BHave%20you%20tried%20this%20tool%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fmanville.org.uk%2Fsoftware%2Ffindlink.htm%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmanville.org.uk%2Fsoftware%2Ffindlink.htm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1290671%22%20slang%3D%22en-US%22%3ERe%3A%20Hidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1290671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20THANK%20YOU%20!!!%26nbsp%3B%20It%20actually%20found%20multiple%20cases%20of%20hidden%20references%20I%20hadn't%20found!%26nbsp%3B%20Even%20though%20a%20couple%20of%20them%20it%20failed%20to%20delete%20I%20was%20able%20to%20use%20the%20list%20option%20for%20it%20to%20tell%20me%20where%20to%20go%20and%20in%201%20case%20I%20had%20to%20use%20VBA%20to%20pull%20it%20out%20(it%20was%20a%20hidden%20name)%20and%20eventually%20I%20was%20able%20to%20change%20the%20value.%26nbsp%3B%20THANK%20YOU%20for%20putting%20me%20on%20to%20this%20and%20THANK%20YOU%20to%26nbsp%3B%3CSPAN%3EBill%20Manville%20Associates%3CSPAN%3E%20who%20actually%20wrote%20the%20software%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1290673%22%20slang%3D%22en-US%22%3ERe%3A%20Hidden%20External%20Reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1290673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20just%20thought%20you%20might%20like%20to%20see%20the%20solution%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Super Contributor

So I think I'm pretty Excel savvy but this problem comes up every so often.  I have copied some info and/or sheets from another workbook and now this new workbook keeps saying it can't re-connect with the previous workbook.  I have searched the entire workbook (including formulas), the entire VBA space, the data model (empty), existing connections (empty) and can't find this link anywhere.  I do the Inspect Document for personal information and it also finds an external link, but I can't. 

Any tips or suggestions?

thank you.

9 Replies
Highlighted
I've experienced the same phenomenon, fortunately only on one workbook...so I'm eagerly awaiting what others might offer as the reason, and even more, what the solution will be.
Highlighted
Try looking at the Name Manager (Ctrl+F3). There might be defined names that refer to an external workbook.
Highlighted

@Twifoo  excellent suggestion, but unfortunately I already checked there also.  

Highlighted

now I've checked all the objects, graphs, and conditional formatting.  I've done additional 'find all' searches to no avail.  

How can Excel be "smart" enough to tell me there is this missing link but not able to give me any hint as to where it is.

BTW, at 1 point I was given the option to 'break the link' and did that but it still warns me. 

Highlighted
Select your preferred option in the Edit Links (Alt,A,K) dialog box, one of which is Break Links.
Highlighted

@Twifoo  thank you again.  as I mentioned I already told Excel to break the link but it still warns me.  After I open the workbook I go to Alt, A (Data Tab) and 'Edit Links' is grayed out.

I wish I could go into an editor and search the 'raw' code behind the workbook to find the link location.

Highlighted
Best Response confirmed by mtarler (Super Contributor)
Solution
Highlighted

@Riny_van_Eekelen  THANK YOU !!!  It actually found multiple cases of hidden references I hadn't found!  Even though a couple of them it failed to delete I was able to use the list option for it to tell me where to go and in 1 case I had to use VBA to pull it out (it was a hidden name) and eventually I was able to change the value.  THANK YOU for putting me on to this and THANK YOU to Bill Manville Associates who actually wrote the software

Highlighted

@mathetes  just thought you might like to see the solution