One way to connect data between two Excel files in SharePoint Online/OneDrive without requiring the opening of both files is to use Power Query.
Here are the steps to follow:
- Open Excel and create a new file.
- Go to the "Data" tab and select "From Other Sources" > "From SharePoint Folder".
- Enter the URL of your SharePoint site and select the folder that contains the Excel files you want to connect.
- Select the files you want to connect and click "Edit" to open Power Query.
- In Power Query, select the table or data range you want to connect and go to the "Home" tab.
- Click "Close & Load" and choose "Only Create Connection". This will create a connection to the selected data without importing it into the current worksheet.
- Repeat these steps for the other Excel file you want to connect to.
- In the original Excel file, go to the "Data" tab and select "Existing Connections".
- Select the connections to the two Excel files you want to connect and click "OK".
- You can now display data from both Excel files in a single table or chart without having to open both files. Any changes made to the source data in the SharePoint folder will automatically be reflected in your Excel file.