Force external link in Excel to break if the file can't be found

7

2

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.

Scribblemacher

Posted 2015-05-15T15:34:45.377

Reputation: 483

Possible duplicate of How to find broken links in Excel that can't be broken with 'Break Links'?

– Burgi – 2016-05-26T20:30:22.547

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 – 2019-02-13T03:57:16.283

Answers

23

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.

outjet

Posted 2015-05-15T15:34:45.377

Reputation: 346

I made a small script to automate the "more forceful" option and it seems to have the desired effect. – Scribblemacher – 2016-05-31T12:32:04.690

The normal option did not help ... There were no data validation or conditional formatting. "A more forceful option" worked :) – Dheer – 2016-07-01T08:27:02.863

1This 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 – 2017-11-29T17:58:28.647

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 – 2020-02-21T06:49:27.147

3

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!

AndyUpNorth

Posted 2015-05-15T15:34:45.377

Reputation: 61

1Could you include a screenshot of where to find this location? – Burgi – 2016-05-26T20:29:24.853

Are you the same user who asked the question or what? – Julie Pelletier – 2016-05-28T05:04:23.760

Deleting all Conditional formatting solved my problem, even though nothing in Conditional formatting referenced an external file. Looks like a bug.... – feetwet – 2020-02-03T19:47:34.353

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"

maciej

Posted 2015-05-15T15:34:45.377

Reputation: 131

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 – 2017-11-29T17:38:18.323

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 – 2017-11-29T22:15:30.957

Asked: 2015-05-15T15:34:45.377

Viewed: 101 110 times

Active: 2017-11-13T18:48:29.743