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.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...