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

TomCon

Board Regular
Joined
Mar 31, 2011
Messages
235
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.
 

Forum statistics

Threads
1,082,250
Messages
5,364,027
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top