Unable to turn off "Save external link values"

[neilduggan]

Hi

I have an Excel 2007 workbook (.xlsx) with links to external data (excel files on SharePoint so the links are http...).

Sometimes the links are not present and I expect to get a #REF error (which is fine as I use IFERROR to return "N/A") but instead, Excel is retaining the last known valid value for these cells. In the Trust Centre settings, I have setup to update all external links and I have unticked the checkbox in "Excel Options" to "Save External Link Values" and I expected this to solve the problem but it hasn't.

Any ideas on what else I can do?

Thanks in advance

neil

[neilduggan]

Hi

Update on this - the problem seems to be resolved when I untick the box "Use 1904 date system". Once I do that and click Ok, all the broken links lose their previously held value (which is what I want!)

However, I need to use the 1904 date system as I want to display negative units of time (i.e. hours behind schedule).

Any thoughts?

Thanks

[neilduggan]

Hi

Back again with a workaround:

I'm going to change the formula where I have the negative value displayed (as "######" when not using the 1904 date system) from:

http:externallink.xlsx

to

=IF(http:externallink.xlsx<0, TEXT(ABS(http:externallink.xlsx),"-h:mm:ss"),http:externallink.xlsx)

This is working for me but it prevents me from applying formatting based negative time values (because it's now stored as text) so any help would be appreciated

Thanks

[neilduggan]

Anyone able to help??