Correcting external links in Formulas Name Manager

Joined
Sep 13, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

There is a macro-enabled .xlsm file produced by my team at work that gets sent to others to populate every month. Any time the file is opened, a catalogue of error messages pops up highlighting links to external workbooks that cannot be refreshed.

I have found these links in the Name Manager box, linking to over a dozen other workbooks. If I delete the names the file goes horribly wrong, but in so many places I do not know where to start.

Can you please tell me, is there a way to search every part of an excel file for either the link or the named formula name, so I can work through each one methodically?

I have tried searching for either the link or the name in the Find & Select option, searching within the formulas and for the whole workbook, but I cannot find any of them.

I would have thought that this means none of these links are actually being used and therefore can be deleted, but then why would the file stop working when I do that?

I am extremely grateful for any help you can provide, and I apologise if this question has been recently answered, I did look but could see nothing.

Many thanks,
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
If the external links are used in formulas in the workbook try searching for a square bracket in the formulas
[ or ]
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
External links can be stored in several places in a file, most of which are not immediately recognisable. Could be a piece of code assigned to a command button for example.
Sometimes it takes a while to locate the culprit.
I normally start removing a sheet or a few at a time to see the change.
Sometimes inspecting the xml format files may help, but this is too convoluted to do manually.
Square brackets a certain for cells, just make sure you search in the whole workbook.
 
Joined
Sep 13, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

External links can be stored in several places in a file, most of which are not immediately recognisable. Could be a piece of code assigned to a command button for example.
Sometimes it takes a while to locate the culprit.
I normally start removing a sheet or a few at a time to see the change.
Sometimes inspecting the xml format files may help, but this is too convoluted to do manually.
Okay, I understand, thank you for all of your suggestions.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
You might also want to check out this add-in. FindLink
Not something I've ever used myself, but a lot of people swear by it.
 
Joined
Sep 13, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You might also want to check out this add-in. FindLink
Not something I've ever used myself, but a lot of people swear by it.
Thank you for the suggestion but I'm afraid our work systems do not permit additional add-ins. Hopefully between bobsan42 and any other methods from within Excel I will get to the bottom of it. Thank you thought
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,044
Messages
5,835,103
Members
430,342
Latest member
sdelan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top