Correcting external links in Formulas Name Manager

[ithoughtiwasokayatexcel]

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,

 

[bobsan42]

If the external links are used in formulas in the workbook try searching for a square bracket in the formulas
[ or ]

 

[ithoughtiwasokayatexcel]

If the external links are used in formulas in the workbook try searching for a square bracket in the formulas
[ or ]

So this found 3 of the links which have been fully removed. Thank you very much. Any other suggestions?

 

[bobsan42]

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.

 

[ithoughtiwasokayatexcel]

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]

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.

 

[ithoughtiwasokayatexcel]

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

 

[Fluff]

Ok, it would also be worth checking any conditional formatting rules.