How to link two EXCEL files in sharepoint?

Question

Good morning,

I have two EXCEL workbooks saved in my Sharepoint file, and I would like to link both so if information in specific columns was entered in book 1 gets added to a specific location in book 2.

I tried to follow the Linking Two Excel Worksheets in SharePoint Online - Microsoft Community guide, but there are options in those instructions which are not on my Sharepoint, for starters, this instruction: "Data tab, go to New Query > From File, choose From Workbook.".

So, I am stuck on how to link my two workbooks.

For example, how do I get column B in workbook 1 to be replicated into column F in workbook 2?

Thank you, and I would appreciate any assistance.

Richard

Answer 1

Dear RichardSpas,

Greetings! Thank you for posting in the Microsoft forum.

The link mentioned in your post describes the process in Excel desktop app, but I believe you are using Excel for the web, right?

If you are using Excel for the web and wanted to create a workbook link, follow the steps below:

• Open two workbooks in Excel for the web. They should be stored in either OneDrive or SharePoint.
• In the source workbook, copy the range.
• In the destination workbook, “paste links” via the right click menu or via paste special on the Home tab.

The gif below provides a brief demonstration.

For reference information, see Workbook Link support in Excel for the web - Microsoft Community Hub

If my understanding above is not consistent with yours, you can also post back and point that.

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Neha | Microsoft Community Moderator

Answer 2

It works if you are the only person who is ever going to use the spreadsheet. Having a formula directly referencing another spreadsheet in Sharepoint puts your computer's name in the formula bc of the file path so when someone else opens the spreadsheet, the formulas are broken.

Answer 3

Thank you

Answer 4

Does microsoft provide any options to support 2 documents that are using SUMIFs in one sheet to push data to the other? I have this exact issues you mentioned and have been trying to resolve it for weeks. Microsoft support is playing phone tag with me trying to get it resolved.

Isnt the point of SharePoint to allow multiple users to work on the same documents at once? There has to be a way around the issue we are experiencing. Lol.

Thanks for the help!

Answer 5

Ross,

Did you ever figure this out? I have the same or similar issue here.

On our company safety training SharePoint site, I have a source/master file spreadsheet that I update regularly. Elsewhere within our company SharePoint I created a destination file using the Excel desktop app and connected it to the source file via Get and Transform Data > From Web and used Power Query to only show the relevant employees and courses for that particular team. (The plan is to replicate this workflow for other teams, but only show the relevant team members and completions applicable to that project.)

The intent is for the destination file to reflect the new data (after I occasionally update the source file) so that when a team member opens the destination file it automatically refreshes so they can see the new completions. However, when users open the file they see the message, "Couldn't refresh data. This workbook contains Power Query queries using data sources that can't be refreshed in Excel for the Web"

Thanks for any suggestions you might have.