Links to other files in error!

[steve case]

[I posted this in the Lounge gripe thread]
[Which is this thread right here~admin]

I take most everyone has seen this dialog box:

This workbook contains links to other data sources.

*If you update the links, Excel will attempt to retrieve the latest data.
*If you don't update the links, Excel will use the previous information.

Note that data links can be used to access and share confidential information without permission and possibly perform other harmful actions. Do not update the links if you do not trust the source of this workbook.

I've stopped trying to rectify the situation and just click on cancel. So does everyone else. Big mistake. If there is a file that changes from time to time, and you're relying on a link to it to make your data correct, and some one moves or removes it! you get the above message. But you have no idea it's your very important file that has been deleted or moved. And Excel as the message says, uses the last data it had access to.

Is there a way to force Excel to display the #N/A error for the broken link instead of telling the lie? Well really, if the file’s been deleted or moved, Excel has no idea what the true value of the "Link" is and is lying when it puts up that last known value and really doesn't tell you except for that opening message.

Very frustrating, the Bill Of Material was moved and then changed, and my Excel file reported the old value just like that.

I've got a huge file with lots of pages and all sorts of people do lots of things including making links to files that after a while get moved or deleted. I can't chase them all down.

So, is there a way to force Excel to "Crash" and put up an error message in the cell where the link is supposed to be?

 

[steve case]

Thanks for the reply and welcome to Mr. Excel

I'm living with the broken link issue, I just click cancel six or seven times every time I open the file and get on with whatever it is I'm doing.

 

[lylefc]

Hi,</SPAN>

I am having a similar issue with Excel 2010. I have multiple excel files linked to formulas in my “Master” spreadsheet, and have added IFERROR sub-formulas to each cell so that if the link file is missing or corrupt, etc., the result should show a “0.”</SPAN>

I do not want the old value to display when the linked file is missing, but rather it calculate the formula as an error since the file is missing.
</SPAN>
Is there a way to do that? Because right now it continues to show the old value unless I double-click each sell for it to update and I need it to do it for all cells when I hit F9 / Shift-F9.</SPAN>


Thanks!</SPAN>

Lyle</SPAN>

[I posted this in the Lounge gripe thread]
[Which is this thread right here~admin]

I take most everyone has seen this dialog box:



I've stopped trying to rectify the situation and just click on cancel. So does everyone else. Big mistake. If there is a file that changes from time to time, and you're relying on a link to it to make your data correct, and some one moves or removes it! you get the above message. But you have no idea it's your very important file that has been deleted or moved. And Excel as the message says, uses the last data it had access to.

Is there a way to force Excel to display the #N/A error for the broken link instead of telling the lie? Well really, if the file’s been deleted or moved, Excel has no idea what the true value of the "Link" is and is lying when it puts up that last known value and really doesn't tell you except for that opening message.

Very frustrating, the Bill Of Material was moved and then changed, and my Excel file reported the old value just like that.

I've got a huge file with lots of pages and all sorts of people do lots of things including making links to files that after a while get moved or deleted. I can't chase them all down.

So, is there a way to force Excel to "Crash" and put up an error message in the cell where the link is supposed to be?