Forum Discussion

Francisco77's avatar
Francisco77
Copper Contributor
Jan 16, 2024

Connect two excel tables stored in Sharepoint

Hi All,   I have two excel files stored in different sharepoint folders. I extract information from one file from the other using the Vlookup formula, but the values are displayed correctly as long...
excel
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jan 17, 2024

Francisco77 

When working with Excel files stored in SharePoint and using formulas like VLOOKUP to connect data between them, you may encounter issues when one of the files is closed. This is because Excel formulas generally rely on the source data being available, and if the source file is closed, the formula may not update.

To create a more stable connection between the two Excel files in SharePoint, consider the following options:

  1. Power Query / Get & Transform Data:
    • Use Power Query (also known as Get & Transform Data in Excel) to import data from SharePoint into each Excel file.
    • In Excel, go to the "Data" tab, select "Get Data," and choose "Combine Queries" or "Combine Queries as New."
    • Use Power Query to merge or append data from both files, and then load the combined data into your Excel file.
    • This way, the data will be stored in your Excel file, and you won't rely on external connections when the source files are closed.
  2. Power Pivot:
    • If your Excel version supports Power Pivot, you can use it to create relationships between tables.
    • Import both tables into Power Pivot, establish relationships, and create PivotTables or PivotCharts based on these relationships.
    • Power Pivot allows for more advanced data modeling and relationships compared to regular Excel formulas.
  3. Excel Linked Tables:
    • Instead of using VLOOKUP, create linked tables in both Excel files.
    • In Excel, go to the "Insert" tab, select "Table," and choose the range of data.
    • This will create a linked table that is connected to the SharePoint data. Repeat this process for both Excel files.
    • You can then create relationships between these tables.
  4. Excel Online:
    • Consider using Excel Online in the browser, especially if your SharePoint documents are stored in OneDrive for Business or SharePoint Online.
    • Excel Online can handle more dynamic connections between workbooks stored in the cloud.

Before implementing any of these solutions, it's important to consider the specific requirements of your workflow, the versions of Excel you are using, and the SharePoint environment you are working in. Choose the method that best suits your needs and technical constraints. The text was revised with the AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Resources