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
=[BookCDrive]sheet1!$A$1.
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.
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
=[BookCDrive]sheet1!$A$1.
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.
