#value returned when reopening a worksheet where functions are linked externally

[zacarhay]

I'm getting a rather annoying and regularly occurring issue where I am reopening a workbook that I have worked hard on with a lot of functions based on data in an external workbook and seeing a #value. regardless of whether I ask excel to update or not update when I get the prompt to update the external links, these values do not change. Now these two workbooks are both stored in the same folder on OneDrive so I don't understand why this issue keeps happening. Why is it simply not just running the function on the most up to date value in the other workbook given they are stored in the same folder?

[#2]

If it is any consolation, I see the same. My answer is a macro that on Auto_Open opens up all linked spreadsheets. I believe it should not necessarily happen if nothing prompts these fields to recalc. But a simple ALT-CTRL-F9 will already upset this delicate apple cart. So with this kind of arrangement, you are only ever one step away from #VALUE! From your description, I might guess that some VOLATILE cell (eg TODAY()) feeds into your external references. That will be enough to push the sheet over the edge. The location of the external data does not really enter the equation. I very much agree with your frustrated closing remark - would not that be nice? I guess one problem with such a feature would be that you completely lose sight of which external data as been updated, and which not. That is, it is dangerous.