Error message when clicking hyperlink in Office: Cannot locate the Internet server or proxy server.
I have an Excel spreadsheet that has an external link to another spreadsheet. What I want to happen is if. MainSpreadsheet.xlsx. SecretData.xlsx When someone opens MainSpreadsheet.xlsx and they don't have SecretData.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 SecretData.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,'SecretData.xlsxData'!$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. I had a similar problem - two solutions were required:.
Check under Formulas Name Manager and remove and external links. 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:. Make a copy of your spreadsheet. Rename it to spreadsheetname.zip (instead of.xlsx). Open file in WinZip or similar. Navigate to xl subfolder.
Delete 'externalLinks' folder. Rename file to spreadsheetname.xlsx. 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. 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 – Nov 29 '17 at 22:15.
Note: To fix multiple links, hold down, and then click each link. Click Change Source. Browse to the location of the file containing the linked data. Select the new source file, and then click Change Source. Click Close. Remove a broken link When you break a link, all formulas that refer to the source file are converted to their current value. For example, if the formula =SUM (Budget.xlsAnnual!C10:C25) results in 45, the formula would be converted to 45 after the link is broken.
Open the workbook that contains the broken link. On the Data tab, click Edit Links. The Edit Links command is unavailable if your workbook doesn't contain links.
In the Source file box, select the broken link that you want to delete. Note: To fix multiple links, hold down, and then click each link.
Click Change Source. Do one of the following: To Do this Fix a broken link to a sheet in another workbook In the Open dialog box, locate the workbook, and then click Change. Fix a broken link to a workbook or other Office document Type the new name or location for the document in the Change links to text box, and then click OK. Turn off automatic updates for linked data. Open the workbook that contains the broken link. On the Edit menu, click Links. The Links command is unavailable if your workbook does not contain links.
In the Source file box, click the broken link that you want to fix. Note: To fix multiple links, hold down, and then click each link. Click Manual. Remove a broken link When you break a link, all formulas that reference the source file are converted to their current value. For example, if the formula =SUM (Budget.xlsAnnual!C10:C25) results in 45, the formula would be converted to 45 after the link is broken. Open the workbook that contains the broken link.
On the Edit menu, click Links. The Links command is unavailable if your workbook does not contain links.
In the Source file box, click the broken link that you want to remove.