Forum Discussion
#value returned when reopening a worksheet where functions are linked externally
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 #valu...
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.