Connecting two excel spreadsheets

Hello Natalia,

Thank you for your question.

Here is the most common and effective method to connect two Excel files:

Using Excel's Power Query (Get & Transform Data)

This is generally the most robust and flexible method, especially when dealing with different layouts. Power Query allows you to connect to external data sources, transform the data, and load it into your internal spreadsheet.

Steps:

Open your Internal Excel Workbook.
Go to the "Data" tab on the Excel ribbon.
In the "Get & Transform Data" group, click "Get Data" (or "From Other Sources" depending on your Excel version).
Select "From File" and then "From Workbook".
Browse to the location of your External Excel file in your Dropbox folder and click "Import".
The "Navigator" window will appear, showing the sheets or tables within your external workbook. Select the sheet(s) containing the data you want to pull.
Click "Transform Data". This will open the Power Query Editor.

In the Power Query Editor:
Shape the External Data: This is where you handle the different layout. You can:
Remove unnecessary columns.
Rename columns to match your internal sheet (if desired).
Filter rows.
Unpivot data if it's in a wide format and you need it in a longer, more structured format.
Create new calculated columns if needed.
Ensure you have a unique identifier column in your external data that you can use to match or identify the information you want to pull into your internal sheet.
Once you've transformed the data as needed, click "Close & Load To..." in the "Home" tab of the Power Query Editor.
In the "Import Data" dialog:
Choose where you want to load the data in your internal workbook (e.g., a new sheet or an existing sheet).
Select "Table" as the display option.
Click "Load".
Setting up Automatic Refresh:

Data Tab -> Queries & Connections: This pane will show your Power Query connection.
Right-click on the query you just created and select "Properties".
In the "Connection Properties" dialog box:
Under the "Usage" tab, you can configure how the data refreshes:
"Refresh every [x] minutes": Set a time interval for automatic background refresh.
"Refresh data when opening the file": The data will update every time you open the internal workbook.
Consider the implications of automatic refresh on network usage and file access, especially with files on Dropbox.

Important Considerations for Dropbox:

File Paths: Ensure the file paths in your formulas or Power Query connections are correct and remain consistent. If you move the files in Dropbox, you'll need to update the links.
Dropbox Sync: The automatic data pull will only work when Dropbox has successfully synced the external file with any new information. There might be a slight delay depending on your internet connection and the size of the file.
File Locking: If the external file is open in another instance of Excel, your internal workbook might not be able to access it for updates.

I appreciate that this sounds a bit technical, and it wasn't covered on your courses, but it is the most robust way to connect two Excel files. Please make sure to follow the steps carefully.

If this doesn't work, please let us know so that we can try a different method.

Kind regards
Marius Barnard
STL