Change data source in Microsoft Teams excel file

Question

Hello,

My organization has recently started to use Microsoft Teams to collaborate on Excel and PPT files. One issue I have run into is when bringing a tab from an outside excel file, then updating the links to the Microsoft teams databook (so the formulas don't link to the previous databook). Prior to teams I would go to data > edit links > change source then select the book I brought the tab into based on where it was saved on my drive. However since the Teams file are hosted, I can't find where to select the file once I click on change source. 

Does anyone know how to solve this? I have been using the find replace function to get rid of the file path for each formula, but this does not work as good as the change source.

Thanks.

Answer 1

Hi KyleFilter,

Thank you for the detailed reply.

When you move the tab from the local drive file to the file on Teams the formulas, the type of value will be displayed as the following picture:

Then when you try to change the source of this moved tab, the Teams file(I named the test file in Teams as "teams.xlsx") will not display in the Change Source box:

There are two workarounds for this problem:

Workaround 1:

If you want to use changing source, then you can open the Excel from SharePoint Online first to get the location of this file. Then you can open the file location in SharePoint.

Then copy the URL of the red line(https://m***.sharepoint.com/sites/Staff/Shared%20Documents/), open the Change Source box and paste the location to the box, press Enter:

However, this method has a disadvantage that it may be necessary to keep the file connected frequently. Otherwise, the data will fail to refresh.

Workaround 2:

This is a tricky but quick method, this method can replace all in the file at once. You can directly use "Find and Replace" feature in Excel, for more details, you can check below picture:

Best regards,

Jazlyn

Answer 2

Hi KyleFilter,

Thank you for choosing our product.

Based on your description, you want to change the data source on Excel file which is uploaded to Teams.

Since you mentioned that the source file was saved on your drive, which means that it was a local file only available on your device, then others could not access the source file on their device.

I tested on my side, if you upload the source file to Teams, then you don't need to change the source file path. When you open the source file from desktop which is uploaded to Teams and change the data, the Teams file will automatically update all data.

If my understanding is wrong, please feel free to post back and correct me.

Best regards,

Jazlyn

Answer 3

Hi Jazlyn,

Thanks for the reply. However your understanding isn't quite correct so maybe I did a bad job explaining. The scenario is I have two files - one is in Team, and one is saved on my local drive. I move a tab from the file saved on my local drive to the file on teams. The tab I moved has formulas that are linked to other tabs. So once I move the tab from the local drive file to the file on Teams the formulas now link back to the local drive file for example =sum('[local drive file]tab2!A5). I have an identical "tab2" in the teams files and I want to change the formula in the tab I moved over to =sum(tab2!A5). 

If I had 2 files saved to my local drive I would normally go to Data > Edit Links > Change source and then select the file from my drive that I moved the tab too. This would change the formula to link inside the excel file the tab was moved to, instead of the previous workbook.

However, on teams when I go to change source, I don't know where to find the Teams file source to select.

Let me know if that clarifies.

Thanks!

Answer 4

Thanks Jazlyn for the reply. Method 2 is what I used to fix my issue. Prior to using teams to host a file, I preferred the change source method, but it looks like for teams using find / replace is the best way to do this.

Thanks for your help!

Answer 5

Hi KyleFilter,

Glad to hear that the problem was solved.

If you encounter other problems in the future, welcome to our community, we will try the best to help you.

Best regards,

Jazlyn