Forum Discussion

georgethemarshall's avatar
georgethemarshall
Copper Contributor
Nov 05, 2021

Can't Find Links and Can't Break Links

At work we have a shared file. When I open it I get this message:  There shouldn't be any links and it's annoying getting the message, so I try to remove them:  However the option to br...
excel
NicolasTld's avatar
NicolasTld
Copper Contributor
Nov 05, 2021

Hello georgethemarshall !

 

Have you check the Name Manager ? There is probably a named range which refers to a cell or a range in another workbook. When you try to find an external link with search function, it will look up only to values, formulas, notes and comments in a specific sheet or the whole workbook, but not in Name Manager.

 

Here is an example :

I have a named range - "WB2_rng_example" - in my workbook which refers to a cell in another workbook - "WB2_example.xlsx" -  (sorry my Excel is in french) : 

 

if I try to find the external link with search function 

 

I will have the following error message : 

nb : in english "Microsoft Excel canno't find the data you're searching for"

 

So, click on "Formulas" pane, then on "Name Manager" and sort by "Refers to". You will probably find the external link.

 

Please let me know if you don't

 

Have a good day ! 🙂

 

Nicolas

 

 

  • georgethemarshall's avatar
    georgethemarshall
    Copper Contributor
    Nov 05, 2021

    NicolasTld 

    Hi Nicolas

    Thank you for your help. I've found the name manager. It has one item in it, but I'm not sure what to do next. When I select it it doesn't give me the option to edit or delete it:

    Thanks

    George

    • KarenTurner-Atkinson's avatar
      KarenTurner-Atkinson
      Brass Contributor
      Nov 05, 2021
      From your screenshot, it says it's Table4 on Open Direct Vacancies so go take out that sheet (move it to a new workbook) and see if the workbook is ok without it. Then re-create it.
    • NicolasTld's avatar
      NicolasTld
      Copper Contributor
      Nov 05, 2021

      This name range is simply a table in your own workbook (as you can see, Table4 is a table in "Open Direct Vacancies" worksheet, range A2:H62. So this not the external link you are looking for.

       

      First of all, try again to find the external link in your workbook by searching for ".xl" (and not ".xlsm" for example as it could be a ".xlsx" workbook) in the workbook.

       

      There are also other possibilities :
      - Are there some buttons in your workbook with macros assigned to it ? If yes, click right on each button, then on "Assign a macro", and check if it doesn't refer to a macro in another macro as the example below

      - Are there graphs in your workbook ? If yes, check that it doesn't refer to data in another workbook

       

      - Last solution : Check all the conditional formatting in your workbook ("Home" pane, click on "Conditional formatting" > "Manager rules" and look at the rules (it could refers to another workbook)

       

      I hope that helps

       

      Nicolas

       

Resources