Find the links for the message "This workbook contains links to other data sources."

Hello there,

There might be cells whose formulas are referencing another workbook. You can view all cell formulas by selecting the formulas tab and then selecting the Show Formulas option from Formula Auditing. The cells that reference another workbook should look something like the below formula.

='[add contents from column A to all other sheets.xlsm]Sheet1'!$A$1

I searched each worksheet and found no external links. If there is no way to find that link easily is there a way to ignore the warning automatically? i.e. Just pick "don't update the links"?

Thanks for your help.

Try the below code to find all external links. Insert the code into a module as a macro. To do this press alt and f11 to bring up Visual Basic. Select Insert from the top menu and then select the module option. Paste the below code in the module. To run the code, exit out of Visual Basic and then select the Developer tab adn the Macros option. Select the ListLinks from the pop up and select Run. This code should produce a new worksheet with a list of all external links within the workbook.

Please Login or Register  to view this content.

Using the built in help feature I ended up "breaking" the link. Hopefully that does not hurt me in the future all though after a visual search over each piece of data I found nothing.

Steps to do what I did.

1) Select the Data tab.

2) Connections group

3) Edit Links

4) Startup Prompt

5) Select the option you want. (I chose to break the link.)

And my problem was gone. Thanks for your input rvasquez.

Is there an option to include the cell and tab of which the external link is in?

For example, "...external link..." Sheet3 A27

Rvasquez's macro worked for me (many thanks). To use the macro I first had to enable the developer tab on the toolbar. This is done by;

On the File tab, choose Options to open the Excel Options dialog box.

Click Customize Ribbon on the left side of the dialog box.

Under Choose commands from on the left side of the dialog box, select Popular Commands.

Under Customize the ribbon on the right side of the dialog box, select Main tabs, and then select the Developer check box.

Click OK.

In the developer tab now shown on the toolbar, select "Visual Basic", click "Insert" from the toolbar, then "Module". In the new window copy and paste Rvasquez's macro. Next select "Run" from the Visual Basic toolbar, then "Run Macro". This put the found link on a new tab, I then did a search (whole workbook) using Ctrl + F, and removed the link.

I realize this is an old post and a solution was found, but in case anyone else has the same problem.

In my case the external link was within Data Validation. I cleared the data validation for the entire sheet and got rid of that annoying message.

After trying all these possible solutions, I was finally able to find one for my case.

Even after checking the data validation of the workbook, the links of the workbook using the macro, etc., it turned out I had conditional formatting rules that were linked to another location. After removing them, the error no longer occurred.

Hopefully this helps anyone who wasn't able to solve their problems from the above solutions.

WOW! After spending an hour and a half looking for the link and trying everything in this thread, I also found it in my conditional formatting!

Thank you danno161 - that was a life saver!

Thanks guys - Similarly I spent a long time searching and after reading this thread found mine in the data validation. Thanks!!

I found my external link. It was in the macro assigned to a Button (Form Control). The trick that 111StepsAhead suggested in going to the Data Tab and selecting 'Edit Links' worked great. However, I was still left scratching my head as to where the link was and how it was caused.

Only when I tried clicking on the button and finding nothing happened did I realize that that was the trouble. This discussion was great in helping me find and stamp out an irritating issue. Thanks.

After a little inspiration from this post (thanks bgwong2476), I've written some code that searches the workbook for any external links, and pastes pertinent information about them into a new sheet. So far it checks:

1. Cells formulas
2. Cell conditional formatting
3. Cell data validation
4. Chart formulas
5. Shape formulas
6. Shape assigned macros
7. Form Control linked cells
8. Pivot Table data sources
9. Regular Table data sources
10. Named Ranges RefersTo formulas

Hopefully, this will save everyone a lot of time in the future. I know it's already saved me some time on a few occasions! And feel free to let me know if I've left something out!

Please Login or Register  to view this content.

Props to McMonkMonk! Your code found a bunch of named ranges referencing an external workbook. Thank you so much for your made code.

McMonkMonk, I believe you forgot a line of code. In the Conditional formatting section, r=r+1 needs to be added like the rest of instances.

I know this is an old post, but it was one of the top hits when googling for this problem.

Wanted to add one more place where I found a link that hasn't been mentioned yet that I saw:

Inside the Name Manager (Formulas -- Defined Names -- Name Manager)

Dunno how a link to an external source got created in there, but there it was.

Amazing... it's solve my problem.

This is great, it returns a reference to an external link. However I still can't find the location of that external link in my Excel spreadsheet. Can the macro be modified to return the precise location of the link? If I can find it I can work out if it should be there and if so what to do about it. Is it in a formula, Name Manager, conditional formatting or where?

Very useful!!