It is my responsibility to keep an Excel Sheet up-to-date for tracking purposes.
Now my colleague from a thrid country asked me if I could also fill his personal Excel Sheet with the same Info.
is there a way I could automatically sync my data to his sheet without having to maintain two seperate sheets?
Both Workbooks are located on OneDrive.
2 Answers
Connect to another workbook
You can create a dynamic connection between an existing Excel workbook and another workbook. You can then refresh the connection whenever the data in the second workbook changes.
There are two methods of creating a connection to another workbook. The recommended method is to use Power Query, which is available if you download the Power Query add-in. If you can't download the Power Query add-in, you can use the Data Connection Wizard.
Power Query
1.In the Power Query ribbon tab, click From File > From Excel.
2.Browse to the workbook.
3.In the Navigator window, select the table or worksheet you want to import, then click Load or Edit.
Data Connection Wizard
Step 1: Create a connection with another workbook
1.On the Data tab, click Connections.
2.In the Workbook Connections dialog box, click Add.
3.Near the bottom of the Existing Connections dialog box, click Browse for More.
4.Find your workbook, and click Open.
5.In the Select Table dialog box, select a table (worksheet), and click OK.
Notes:
Worksheets are referred to as "tables" in the Select Table dialog box.
You can only add one table at a time.
You can rename a table by clicking on the Properties button. You can also add a description.
6.To add more tables, repeat steps 2 through 5.
7.Click Close.
Step 2: Add the tables to your worksheet
1.Click Existing Connections, choose the table, and click Open.
2.In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a Table, PivotTable, or PivotChart.
I don't have the rep to leave a comment, so I hope this elaboration as an answer is OK. I was having the same issue and spent a good week searching for answers. This is the post that got me up and running, and I think I can add to Racoon's response.
I have data in a OneDrive for Business folder (in Excel file), and wanted to develop a spreadsheet stored locally that would link to the data. I did not want to install any additional tools such as Power Query. It seemed like a reasonable goal.
I was still having trouble, even with these instructions, until I realized I was missing something in Step 4 above. It says
- Find your workbook, and click Open.
I had been trying to use the OneDrive share link for the Excel file, with no success. What I should have been using was the share link for the file's folder.
To avoid my mistake, navigate to the folder containing your data file in OneDrive, and click the ellipsis. You will see a bubble containing folder information and a share link. Copy and paste this link into the URL bar of the Browse For More window. I did not expect this, but it will show the folder contents including your data file. Select the data file.
This will allow you to proceed to the next step, which is to link to a table in the file. If you need multiple tables, you will need to create multiple links.
I assume you will
- Need to share the file with any user who needs to use the link, and
- That the user would need to have an account in your organization and be logged in to it.
