11

I have an Excel spreadsheet that has an external link to another spreadsheet. What I want to happen is if

  • Main_Spreadsheet.xlsx
  • Secret_Data.xlsx

When someone opens Main_Spreadsheet.xlsx and they don't have Secret_Data.xlsx, I want the external links to break (meaning they get #REF! or whatever in the cell instead of real data). What happens now is they see the real data from Secret_Data.xlsx from last time the links were updated.

How can I do this so that if the file in the external link is missing, the data is cleared?

=LOOKUP(A30,'[Secret_Data.xlsx]Data'!$A$2:$B$20)

So the code above should make the cell empty (or error, or anything else) if the referenced xlsx is missing. Right now, it leaves the existing data from last time the link was updated.

2
  • This can occur from conditional formatting. I fixed this by going to each tab and clicking Home>Conditional Formatting>Clear Rules>Clear Rules from Entire Sheet. You then have to save, close, and reopen. Upon reopening the links will no longer exist. If you had any desired conditional formatting you'll need to reapply.
    – kackleyjm
    Feb 13, 2019 at 3:57

3 Answers 3

26

I had a similar problem - two solutions were required:

  1. Check under Formulas > Name Manager and remove and external links
  2. Find any cells with Data Validation that may be referring to an outside link. (Home/Find & Select/Go To Special/Data Validation/All)

A more forceful option:

  1. Make a copy of your spreadsheet.
  2. Rename it to spreadsheetname.zip (instead of .xlsx)
  3. Open file in WinZip or similar
  4. Navigate to xl subfolder
  5. Delete "externalLinks" folder
  6. Rename file to spreadsheetname.xlsx
  7. Open spreadsheet in Excel - choose to repair sheet

Upon repair, Excel should tell you more detail about what links had to be broken manually. You can either use the new sheet, or use the information you learned to go remove the links manually.

4
  • I made a small script to automate the "more forceful" option and it seems to have the desired effect. May 31, 2016 at 12:32
  • The normal option did not help ... There were no data validation or conditional formatting. "A more forceful option" worked :)
    – Dheer
    Jul 1, 2016 at 8:27
  • 1
    This did not work for me in Excel 2016. In fact excel reloaded the missing folder. There were some other xml files at the root but I wasn't sure what to break.
    – Shawn
    Nov 29, 2017 at 17:58
  • Excel 2016: First use FindLink to detect & delete links in your sheet, safe and afterwards do the steps from solution. should be working now.
    – bucky
    Feb 21, 2020 at 6:49
4

After checking objects, formulas, Names, chart titles and data series, I discovered that my external reference was in "Conditional Formatting". However, there was no reference to another workbook, such as [Budget.xls] in any of the fields or conditions. Only afterward was I able to successfully break the link in the "Connections" Group. I hope this helps!

3
  • 1
    Could you include a screenshot of where to find this location?
    – Burgi
    May 26, 2016 at 20:29
  • Are you the same user who asked the question or what? May 28, 2016 at 5:04
  • Deleting all Conditional formatting solved my problem, even though nothing in Conditional formatting referenced an external file. Looks like a bug....
    – feetwet
    Feb 3, 2020 at 19:47
0
  1. Click the office button logo or File menu at the top left and choose "Options" or "Excel Options" (depending on your version)

  2. Under "Advanced", in the "When calculating this workbook" section, uncheck "Save external link values"

2
  • This did not work. I want to break the external links but it won't let me. I checked all the sheets and cannot find any formatting or references being used.
    – Shawn
    Nov 29, 2017 at 17:38
  • I suppose this wouldn't catch certain kinds of external links. Other places I've seen them hiding: * Named ranges * Conditional formatting * Chart sources * Chart data label sources * Data validation Similar to the "forceful option" in the accepted solution, you should be able to track down exactly which part of the workbook contains the link by searching for the linked filename within all the unzipped files. On a Mac/Linux system you could do this by using grep -RH "LinkedFileName" * in the shell
    – maciej
    Nov 29, 2017 at 22:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .