Musings on behavior of external references in cells in OneDrive shared workbook on Windows/Android


Active Member
Mar 31, 2011
Office Version
Windows, Mobile
I don't really have a question here, just wanted to share this experience, partially as a warning, but if you have any comments on how to improve, please post! Its a bit hard to describe but i'll try to simply. Its "complex and intricate"...and unfortunately "irregular and unpredictable" in numeric behavior. Sorry its a long description.

There are two workbooks in question. One i'll call [BookOneDrive], the book stored on OneDrive, and one i'll call [BookCDrive] for the book stored on my C Drive on a Windows 10 PC. [BookOneDrive] is a shared workbook and is always open on both a Windows PC and an Android phone. Basically, i do data entry in both, and it "basically works". That is, values entered on Android show up in windows and values entered in Windows show up in OneDrive. At then end of the day, i have the union of all the values entered in either place, nicely in one workbook.

But, for formulas its a different story. Say that [BookOneDrive] has in cell A1 an external reference to [BookCDrive], so it looks like

As i am looking at [BookOneDrive] on Windows, if the value in cell sheet1!A1 in [BookCDrive] changes, then the value in [BookOneDrive] also instantly updates [of course] as both books are open on the Windows PC.

And, in [BookOneDrive] when viewed in Android, the "last known value" correctly shows in the cell in the workbook grid, but in the data entry bar of Excel, it shows "this cell is an external reference that can't be shown or edited...". OK, i accept that for Android Excel, external references are frozen.

But, the question is "how long are external reference cells frozen for and when do they update by the act of saving the windows version of [BookOneDrive]"? (since the external reference in the Windows version of the very same file is indeed correctly updated). My answer to those questions is "it is irregular as to how long they are frozen for" and "unpredictable as to when they update".

Here is what i hoped would happen.

I have [BookOneDrive] open on Windows. I update the cell in [BookCDrive], so [BookOneDrive]'s external reference instantly recalcs and updates. I Save the workbook on Windows. Then, when I look at [BookOneDrive] on windows, i see the new value in the cell. So, when i look at [BookOneDrive] on Android, i expect to see the very same value in that same cell of the same workboook, since i just saved that workbook to OneDrive. It would seem like both places, viewing the same cell of the same cloud-stored workbook, should show the same value. That is, specifically I am not depending on the external reference from the Android version to get the updated value, i am depending on the act of saving on the Windows PC (where the updated value is indeed updated) to get that new value over to Android.

But, things are not working as I had hoped.

Sometimes i do get the updated value to Android via the above described set of actions (key one: save on Windows version) and sometimes I do not. Sometimes the "old" value for the external reference value appears in [BookOneDrive] on Android (even after saving [BookOneDrive] on Windows)...but, then...eventually the new, updated value does appear on the Android version. After a number of repeated saves on Windows, maybe after the 5th save or the 10ths save on Windows, suddenly that updated value does get updated in the Android view.

For example, I have a formula in [BookCDrive] that is dependent on TODAY() and so it changes once daily, increments. Looking at [BookOneDrive] on both Android and Windows as I write this, the same value does not show in the same cell when looking at [BookOneDrive] on Windows vs. looking on Android. It is about 10 days out of date on Android in fact, despite numerous saves on both Windows and Android of [BookOneDrive] over the last 10 days. But, yet other values for cells also with external references are much newer than 10 days old, and are correctly updated in [BookOneDrive] as viewed on Android. And apparently even that cell's value was "occasionally" updated, as it is only 10 days old (it is a counter that started about 60 days ago).

So, my conclusion is that the behavior is "irregular and unpredictable".

Any interesting comments on this other than "oh well, things don't work as you hoped"? To me this "irregular" behavior is disturbing as it means you can have numeric errors spread throughout the workbook if you cannot depend on a specific model for recalculation.

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...