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.