9

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.

  • 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 '19 at 3:57
25

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.

| improve this answer | |
  • I made a small script to automate the "more forceful" option and it seems to have the desired effect. – Scribblemacher May 31 '16 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 '16 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 '17 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 at 6:49
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!

| improve this answer | |
  • 1
    Could you include a screenshot of where to find this location? – Burgi May 26 '16 at 20:29
  • Are you the same user who asked the question or what? – Julie Pelletier May 28 '16 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 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"

| improve this answer | |
  • 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 '17 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 '17 at 22:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.