External link in Excel to another Excel sheet

I was asked to look at an Excel sheet that was giving an error stating that an external connection could not be established. Excel online was stating that there was a link in it and so did Excel. That was the easy part, the hard part was finding the external connection and removing it. Excel (desktop) will warn you of any link that cannot be established, and Excel will ask you what to do with the error. You can do a few things (see image below)

Edit links options

 Everyone would normally choose "break link" and let the problem be fixed, however Excel doesn't allow this. Excel won't state why not or where the reference is but  it won' t remove it either. So how to go about finding this link and removing it? Below I have put my questions I asked myself and the steps I took to see if the link was there.

  1. In which worksheet is the problem?
The answer to this question is pretty simple, remove the worksheets one by one. Excel shows in the info if there is an external connection in the Excel sheet, so if you remove it this notification goes away. There were 3 worksheets and I removed the first one that had all the data inside. Jep that worked, the error disappeared. The problem has to be in this worksheet. Check.

Edit links to files

  1. Is all the data here? Am I seeing all the columns that are in the Excel sheet?
I removed all the filters to make sure that all the data was showing. Hidden rows or columns can be shown by doing a Check document which lets you remove the hidden columns and rows. Check.
  1. Is the external reference in the data in the worksheet? Is it in a formula? Fixing this question you can do 2 ways.
    1. Let's start by using search to look for "[" or ".xl" references in the formula. Another one that could work is #Ref! that is the error Excel gives if it can't find the link.
    2. The second option is the simple one (Just make sure you are testing on a separate Excel sheet or don't save it!) Remove all the data in the worksheet. Select the whole worksheet and press delete
If the link is in the formula it will show up or the error will disappear in the info screen.
  1. Is it in the VBA code?
I did this and did a search here as well but couldn't find anything.
  1. So what is the one place a formula can be used but doesn't show up in search? DATA VALIDATION!
A yes, data validation is being used to create dropdown lists in the Excel sheet. Data validation has a cool feature that circles all the bad references in the formula. Follow the steps below.

  1. Go to Data > Data validation and click on the arrow below data validation.
  2. Select circle wrong reference …..
  3. If there is a mistake in the formula Excel will circle the ones that are not referencing correctly
  4. Voila there it is! The hidden URL.
  5. Click on the cell and click on data validation to show the formula being used. Ah yes the URL to an External Excel sheet.
  6. Click on Clear all to remove all the data validation on that cell.
  7. Should you wish to do this for the column do the same steps but instead of selecting the cell choose the Column.


It's been a hell of a journey finding the wrong URL but I found it in the end. On internet there are a lot of different ways to find the external links even complete scripts. I however could not use scripting because of limitations on the computer I was using. Luckily I found it.

Comments

Post a Comment

Popular posts from this blog

Azure Information Protection (AIP)

Image
Let's start with one of my favorites (till now). Azure Information Protection (or AIP). Just imagine the following example: You are working on your Word document and you want to send this document to an external person. You e-mail that document as an attachment. The person opens this document, reads it and puts it on an USB stick to take with him. The USB stick get's lost! Now the document can have fallen into the wrong hands! Now what? OK this is a little bit of a roman but hey it could happen! Active Director Rights Management  First let us go back in time to when AIP didn't exist and there was  Active Directory Rights Management Services .  This was a server role that could be installed on Windows Server 2008 R2, it exists since Windows server 2003! Back then it was called RMS. The RMS client is available  from Windows 2000 and later. So what does RMS do? To explain in simple English: Information with RMS on it has certain policies with it  that would remain
Read more

Tiles modern UI

Image
Back in SharePoint 2013 Microsoft introduced "Promoted links" a feature which enabled users to create tiles quick and easy without any custom code. Since then I've seen Promoted links being used a lot of ways, some better than others. However creating tiles with an image in the background was always a nasty thing to do. People would use the weirdest images as background to indicate what it was, totally not user friendly. Especially the choice of the color was very "weird" you would have tiles with all sort of colors and images (see image below as an example). Promoted links always had one big problem; Scaling! It's not responsive in a way you would want to have it. Since Microsoft introduced Modern UI they have also introduced the webpart "tiles". Tiles enables the user to create custom tiles within their SharePoint site, quick and easy. Cool features of the Tiles webpart. Tiles are responsive! They resize when the screen gets smalle
Read more

Azure Active Directory (AAD)

Image
In my last post I talked about Azure Information Protection (AIP). It was a way of getting a good sense of security in the Cloud. Today I want to talk about a very important feature in Azure: Azure Active Directory (AAD). AAD is a good place to start with security. Users that are allowed or prevented from logging in is your first line of defense. In preview AAD is not something radically new. Microsoft created the Management portal om the new Azure Portal to make it look and feel like the new portal. At this moment the most users will have to use the old portal for AAD but once the new management portal is out of preview we are good to go! What has changed? So who cares? It's a new Mangement portal for AAD. To be honest nothing has really changed, Microsoft just changed the look & feel of the new portal. 3 versions There are 3 versions of the AAD: Free, Basis and Premium. See the link below to decide what versions of AAD you need. Overview AAD subscriptions F
Read more