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

Hello,

I have been able to link external Web Excel Workbook Tables by going to Menu Data and obtaining data from a Sharepoint Folder and drilling into the Excel workbook containing the Table . I choose the target Table during the phase of constructing the Query.

The benefit is that ;

1. You get a Relative Path (not a Absolute path) in the M code. (Not dependent on your PC's ID :))
2. Unlike Workbook Link, you don't get #REF in the cell formulas if something breaks during updates in the source Workbook (eg: line count changes)
3. No need to use the Workbook Link Manager panel (Query's Data Source params suffice)
4. Update All works in Excel Application . (Workbook Links work in Web Excel, but are subject to the corruption of Cell Formulas (#REF insertion) if source data changes line count)
5. Does not break for someone accessing the Workbook from OneDrive on a different PC (as long as that person has rights to the Sharepoint Library where the Workbook is stored)

Here's the Fragment of M code that worked for me :

= Excel.Workbook(SharePoint.Files("https://XXXXXX.sharepoint.com/sites/Equipecompta/", [ApiVersion = 15]){[Name="CAFoutils_Base usagers APL-AL_V2.xlsx",#"Folder Path"="https://XXXXX.sharepoint.com/sites/Equipecompta/Matrice CAF/MATRICES CAF/"]}[Content])
= Source{[Item="APL",Kind="Sheet"]}[Data]

Hope this helps

Answer 3

Marc, For the moment we have moved past this problem, but I will refer back to your comments if we revisit the issue. Appreciate your response.