How can data be connected between two Excel files in SharePoint Online/OneDrive without requiring the opening of both files?

edit

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:

1. Open Excel and create a new file.
2. Go to the "Data" tab and select "From Other Sources" > "From SharePoint Folder".
3. Enter the URL of your SharePoint site and select the folder that contains the Excel files you want to connect.
4. Select the files you want to connect and click "Edit" to open Power Query.
5. In Power Query, select the table or data range you want to connect and go to the "Home" tab.
6. Click "Close & Load" and choose "Only Create Connection". This will create a connection to the selected data without importing it into the current worksheet.
7. Repeat these steps for the other Excel file you want to connect to.
8. In the original Excel file, go to the "Data" tab and select "Existing Connections".
9. Select the connections to the two Excel files you want to connect and click "OK".
10. 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.