Fromulas are replaced by #REF

Occasional Visitor

I need help and I can't find anything that answers this specific problem. At my job our excel worksheets use formulas that reference other excel worksheets found on another server. Everything works fine most of the time but every so often the formulas return with #REF. We dont delete anything so thats not the problem. I think we just sometimes lose communication with the server so the worksheet cant reference the data. The problem is when this happens all the formulas get replaced with #REF and most of the time people dont pay attention and end up saving it that way. So next time someone opens the worksheet all the formulas say #REF meaning all formulas are erased as if someone typed over them with #REF. Having some very large worksheets I'm sure you can imagine the pain of having to replace all the formulas. Why in the world excel replaces formulas is mind boggling to me. Is there a way to prevent this from happening? Losing communication with the server will always happen but the formulas should stay intact, even if it's saved with #REF in the field, and should come back to normal when the server is back up. Maybe theres a patch we can download? Thanks for any help.

1 Reply
I would advise to pull in that data using Data, Get Data, From File, From Workbook and set the table to refresh when the file opens (and perhaps every 60 seconds after that). Then have your formulas point to the newly added sheet. If the connection now fails, you will get a refresh error, but your formulas will remain intact.