Forum Discussion
Help on making reference to multiple excel sheets saved on Microsoftteams
Hello all,
Am trying to automate data entry between separate Excels on teams. The scenario is I have 2 excels, let's call them "Source" and "Consolidated", my users will fill the information in "Source", and I want it to auto populate into "Consolidated" to save time instead of manually copy pasting.
Currently I have a workaround to sync the "Source" sheet on Onedrive/sharepoint, and the "Consolidated" file just uses a "=" cell reference to pull up the necessary. It seems to work as long as we manually press sync and update, but am wondering if there is a better way to do it please?
Additionally (stretch goal), if there were multiple "Source" files, could "Consolidated" take reference to all of them and autopopulate as well?
Thank you!
P.s below is the few links I took reference to for the current method
https://www.summarypro.co.uk/blog/excel-drawing-information-from-sharepoint-files.aspx
As soon as you sync the file on SharePoint to local drive it is updated automatically in both ways, changed you it on cloud or locally. Didn't catch why you have to repeat that each time.
If you use Excel for web (same as for Teams) it supports now links to external files, it's not necessary to land the file into local drive.
If Consolidated is used locally, another alternative could be Power Query.
If you'd like the files work the same way on web and locally more, that will be more complex scenario.
As soon as you sync the file on SharePoint to local drive it is updated automatically in both ways, changed you it on cloud or locally. Didn't catch why you have to repeat that each time.
If you use Excel for web (same as for Teams) it supports now links to external files, it's not necessary to land the file into local drive.
If Consolidated is used locally, another alternative could be Power Query.
If you'd like the files work the same way on web and locally more, that will be more complex scenario.
- Hello, thanks for the reply. I think I was not very clear...
Just to clarify what you meant by "If you use Excel for web (same as for Teams) it supports now links to external files, it's not necessary to land the file into local drive.", you mean if I used "=" to reference between the two sheets whether on Teams or excel for web, it will work? I had always thought it couldn't based on what I saw on the forums.Yes, in Excel for web it works. It looks like
First time the link is added you need to click Trust workbook links, but that's only first time.
Initiating the link is the same way as in desktop - open both workbooks in browser, in cell start with =, click on the cell in another workbook.
