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

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.

Solution 1:

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.

Solution 2:

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!