microsoft excelsync
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.
I haven't tested it on your situation, but typically I use paste special to accomplish this:
Select the e-mail addresses that result from your formulas and copy them.
Select the first cell from the same range you just selected.
Go to "Paste Special" and choose "Values"
That will paste the e-mail addresses in without the formulas and you can sort as you please.
I suggest using a PivotTable with AgentName as the Report Filter:
If Agents have so many clients that the width would be a problem, drag Client from Column Labels to Row Labels and change Design > Report Layout to Show in Tabular Form.
Best Answer
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.