Formulas with External Workbook reference to Workbook on OneDrive in Office365 get stale results!!

Question

I am finding that formulas that reference an external workbook on OneDrive, in Office365, are not recalculating consistently. This, if confirmed, would seem to be a serious issue. I have never seen this before the installation of Office 365. Sorry the explanation is a bit long but i wanted to be very detailed. Since this is a calculation problem it is potentially very serious.

I have two cells in a workbook, that both reference the same cell in an external workbook that is on OneDrive. Side by side, i can see that they have different values. One of the values appears to be a "chached" (stale) previous value, not the current value. So, i do not understand why it is not being updated as one normally expects with Excel. The workbook that these formulas reference is also open. The two values remain different despite "Calculate Now", despite entering things in cells that would cause recalc, and despite saving either or both workbooks. The only thing that corrects the "wrong" value, is to edit its formula text, and then, when I hit return, it then returns the correct value (but now that new currently correct value becomes the value that is 'stuck' as that result).

I notice that the two cells refer to the cell differently, in the following two ways:

(1) =[D4p_enter.xlsx]daily!$I$17

(2) ='C:\Users\tomcx\onedrive[D4p_enter.xlsx]daily'!$I$17

As i understand it, since the workbook D4P_enter is open, then the formulas should be in the first form and not show the entire path (which is what you expect when the externally referenced workbook is not currently open). And indeed the first one is the one that returns the "correct" value. So, I do not understand why Excel does not change expression (2) to (1) when the workbook is opened. (any ideas?).

When i close the workbook D4p_enter, the formula (1) is updated and now reads:

(1a) ='https://d.docs.live.net/da6e7d8e7901482d/[D4p_enter.xlsx]daily'!$I$17

But formula (2) remains unchanged (and still with a stale value).

Re-opening D4p_enter, formula (1a) now returns to its original form as formula (1), but formula (2) remains unchanged both in how its formula is expressed and in the value that is returned.

If I edit the formula text in (2), erase the "7" at the end say, then re-type it, when i hit return it updates with the current correct value. But, the form of the formula still does not change, and now this value is "stuck" in that formula, and it will continue to not update as the value in the referenced workbook dynamically changes, unless i repeat the process of editing it.

All i can ask is, can anybody interpret what is going on, and what i can do to make sure that Excel is recalculating 'normally' every time the value in the referenced workbook changes? I simplified this example to see it, but of course other cells reference these values and they too are wrong, and so this is a killer type problem.

Much thanks for any observations or help you can offer.

Tom

Answer 1

Actually, I can't replicate it in a new workbook. When i just tried the same thing in two new workbooks, it seems to behave more as I would expect.  But, the workbook(s) I describe, it still behave exactly as described, even after multiple openings and closings. I'm trying to figure out "how did it get into that state"?

...Any ideas...please post a response.

Thanks!

Answer 2

I am not able to replicate this. Can you try creating a new workbook and test to see if that same thing happens?