Links between spreadsheets in Excel Online
It would be nice for links between spreadsheets on my One Drive to work.
Thanks for all of your comments and votes supporting this suggestion. We are now in the planning stages to support creation and refresh of external links for workbooks opened in Excel Online.
Thanks – Micah M [MS Excel]
Any update here? G Suite's IMPORTRANGE and QUERY functions allow for flexible rolling up of data collected in disparate sheets into a single parent sheet. This is a very powerful tool for data collection and reporting and it would be nice if Microsoft included similar functionality in Excel Online.
It would also have been nice to have known that this feature does not currently work, before I switched on the One Drive function when setting up my new laptop! Basic requirement for it to be any use at all for excel users. Caused me a lot of problems! Really disappointed with Microsoft on this one.
Any update on this?
At this point I would even take Excel spreadsheets, that are stored in SharePoint Online, to be able to link to each other only when opened in the app (Excel desktop).
I have tried to do so and all appears great, until I save the documents. Upon opening again, all of my formulas that reference data from another spreadsheet are broken.
This is beyond ridiculous. Fix this. Please. SharePoint Online could be great, but is not as a result.
Basically, the situation is you want to read some data from file A and have it available in file b.
So, you take your data in file A and convert it into a Table. Tab insert/table.
Give your table a name in Design/ Table name, tb_example, for instance.
Once your table is set in file A, close it and open file B. MAKE SURE BOTH FILE A AND FILE B ARE IN ONEDRIVE.
In file B go to TAB Data/ From other sources/ From XML Data Import and browse file A. Make sure when searching the “All Files” option is chosen. Then select the sheet where your table is in and OK. Choose whether to insert either in the existing worksheet or in a New one. OK again.
You will se that the table in file A will appear in File B, meaning the info is now available in spreadsheet B.
From now on, every time you change any data on your file A table, you just need to go to TAB Data/ Refresh All in file B to have your data updated.
The real workaround is to have the file B updated every time you open the file. To do so, choose any cell in your recently linked table in file B and go to TAB Data/ Connections, choose File A, / Properties / check in “Refresh data when opening the file” / OK / Close.
By doing this, every time you or any user opens the file B, the data will be updated.
Of course, this does not happen in real time. So, if the data in file A changes while having the file B open, you will need to refresh. But this is an issue that any how you will always have when working with tow or more linked files.
Hope this helps.
Alejandro, can you provide more details on your solution?
Hi. I’ve been listening to the comments on this issue and it is really amazing there is no answer so far.
I’ve found a workaround for my needs. Working with tables and making all of the files get refreshed automatically when open, it simulates linking between spreadsheets. Obviously, it takes time to settle the whole thing down.
To my little IT understanding, if this workaround is feasible with the available tools, it shouldn’t be so difficult for you guys at windows to provide a solution.
It is a matter of willingness to fulfill your customers needs.
Carl Boccuti commented
ANSWER US YOU COWARDS
Alex G commented
If it was my decison I'd just go back to G-Suite and continue using the very reliable Google Sheets. Unfortunately it was a senior decision due to a new IT director and we are now suffering the migration from G-Suite to O-365. The poor quality of the Excel Online software has had a massive detrimental impact to the collaboration and productivity in my office... I dare consider the monetary impact of lost work on top of other costs. We haven't even turned off G-suite yet, this being one of the many reasons. I've explored linking sharepoint lists together using PowerApps as an interface but both of these products are very clunky and feel like unfinished early alpha releases... added to the advanced knowledge required to make these tools work.
Please hurry up and put some work into providing good quality cloud collaboration tools that work for your customers. Microsoft Office was great in the 90s but is struggling to keep up with today's standards.
Thomas Maier commented
We are currently experimenting with Excel xls and doc files in combination instead of using xlsx and docx. It's a terrible work around, but it looks promising. Hope Microsoft will soon offer a fix for the topic, as doc and xls files are outdated for more than 10 years.
An interpretation could be that Excel is meant to be driven by a person and if Microsoft added links between spreadsheets in Excel Online it is essentially 'unattended server updates*' which is not acceptable to Microsoft because of security risks. On the other hand Microsoft could be working on this. It would be nice to receive an update from Microsoft.
*From memory, Microsoft deprecated some C#/Excel development libraries for this reason. Instead of calling Excel from C# via the libraries, changes needed to be made by native C# libraries.
Almost 1 year later and do you don't have a solution? Do you have updates or alternatives of formulas to use? Please we need this solution. G-suites is really good for the IMPORTRANGE and we need a solution wil excel .Thanks!
google is eating your lunch!
Timothy Stamper commented
Ubelievably disappointing that there are 260 comments - many recently simply asking for an estimated date the functionality will be rolled out since it has been posted 9 months ago as "Planned". Being able to communicate planned roll-out dates is software development project management 101 (or lower...pre-K, perhaps). It's freakin' amateur hour.
Per comments, this is business-critical to many. I'm currently evaluating use of Google Sheets vs. Excel online for business, and linking is probably the #1 thing that currently tips the scale to Google for us presently. I'd prefer to choose Excel, but can't with this missing functionality. I, like so many others, just want to know when it will roll out.
Alejandro Romero commented
I’m considering to use 365 Home Edition. Yet, without having the possibility of linking spreadsheets I would have to go looking for another solution. Do you have any update on when we will have this functionality (if there is a date)?
Cristina Idrobo commented
This problem affects a lot our work, because we can`t link files and use advantages that Excel brings
Do you have any progress?
Francisco Rodriguez commented
NO se sincroniza correctamente los archivos en One Drive. Se trata de abrir los archivos excel y se abre en versión web
importantísimo que arreglen esto para poder volver a usar sharepoint
Eduardo Gallo commented
We need to be solved as soon as possible, because we work on the web.
Darwin Zambrano commented
We need help about this problem. It has been very difficult work with this issue.