Updating locally synced workbook links after the supporting file is changed in Sharepoint web app
We have a master summary excel spreadsheet which is linked to cells in supporting workbooks. The files are synced locally on my computer but saved on SharePoint. This way, other users make changes to the supporting workbooks using web access. Then, I open the summary excel spreadsheet saved locally and update values to received the new information. Even though the web version does not supporting linking between workbooks, this has worked in the past since all files are synced locally and the master is refreshed using computer app locally. Beginning on Friday 10/18/19, we started receiving an error for each linked workbook saying "Links to ABC.xlsx were not updated because the ABC.xlsx was not recalculated before it was last saved". Once clicking okay, the links do go ahead and populate the new numbers but if you look at the Edit Links box, it says "Warning: Source not recalculated". This isn't true because the source was recalculated and populated. We have checked all our application setting and formula settings are set to Auto-Calculate. We've also been able to duplicate the issue on a completely different computer and OneDrive account so it appears this might be the result of a software security update. The error will go away once the file is opened/saved locally using desktop app, but will persist as long as the most recent revision was made online in the web version.
Hi Lee! I submitted a ticket to Microsoft Support and kept being passed around between Excel desktop support and Sharepoint/Excel web app support and neither were able to help with the issue. Eventually I gave up when it was clear they would not assist and just kept pointing fingers between the two systems. I am no longer using this spreadsheet and the summary was hosted on a different user's computer, but here is what I recall we did as the workaround. That user would open the summary spreadsheet (locally) and navigate to the data tab. He would click Edit Links and then click Update Values. I believe this cleared the "source not recalculated" error. You can use ctrl or shift to highlight multiple source links at once or use ctrl-A to highlight them all. In our case, the workbook would freeze if he tried to update all at once so he would go through and "update values" on like 10 sources at a time. Hope this helps!
I am having the same issue, has an answer ever been found? Have googled a few times and see the question popping up but never an answer.