Forum Discussion

liseg95's avatar
liseg95
Copper Contributor
Mar 01, 2022

LINKING TWO EXCEL FILES ON SHAREPOINT

Hello there, 

 

I've been having a problematic I'm trying to solve, I think it is very easy, but don't find the way to do it, so I thought asking directly would be easier 😉 

 

So, I have two excel files open my Sharepoint, located in different places.

The two excel are almost similar, except that on one, I need more columns than on the other one to add specifications on it

 

What I wanted to do to avoid doing copy/paste all the time, is to link the two excel files : one would be the "mother" : the one I would fill directly, and the other would be the "child" : in which the info from the mother are filled automatically, and I complete by hand the rest of the columns 

 

I did an easy link, like that : "='[2022 - Suivi des commandes.xlsx]suivi cde '!A3", and I extended the formula all the way down. 

PROBLEM IS  :when I delete or add a row on my mother file, it does not have the same effect on my child file, 

 

What yould be, according to you, the best formula to use so that it copies the whole column and not line by line like I did ?

 

Thank you by advance 🙂

Excel on Mac
Formulas and Functions

5 Replies

Sort By
  • mtarler's avatar
    mtarler
    Silver Contributor
    Mar 01, 2022
    So to my knowledge an in cell formula like that will not update unless that other workbook is also open. A better way is to use Power Query (Data -> Get Data). That said, I would consider the 'master/mother' to be the sheet with all the data in it. You add calculations columns or query data from that data import but I'm concerned if you add hard coded values to rows they may not line up after an update. I'm not expert in Power Query, and only have used it to query data for processing and not ever tried adding data (mainly because I didn't think it would work but the power of excel has surprised me often)
      • mtarler's avatar
        mtarler
        Silver Contributor
        Mar 02, 2022
        What about Power Automate? liseg95, you said they are on Sharepoint:
        Excel Online + Power Automate
        Create flows that automate repetitive work tasks with Power Automate—and trigger right from your Excel data. Download the app

        I have used Power Automate to automatically update an Excel file based on form inputs but maybe you could do excel to excel

Resources