Link Data in Excel - Automatically Link Client Data from a Master Spreadsheet

Link Data in Excel - Have a master spreadsheet that automatically updates the client data into separate sheets. This is done in Excel with master sheets linking to sub sheets. This video shows how to set up a master list and the sub sheets with Microsoft Query.

You can view the blog at https://autom8now.com.au/2019/08/18/how-to-link-excel-data-over-multiple-sheets/

#autom8now #exceltip

For more tips subscribe to my channel at https://www.youtube.com/c/autom8now?sub-confirmation=1

To record and edit the video I used:
* Camtasia or SnagIt, find them at - https://techsmith.pxf.io/9jqk5

To create the thumbnails I use:
* Canva, find them at - https://canva.pxf.io/1ddxm

See previous live events


Video script
Would you like to be able to view a master list of say client activities, or client support queries – and then have a sheet in Excel for each of the clients automatically.  Let me show you how.
Have a worksheet ready with that master list, one column being client names. And then a blank worksheet for each client. And save it as a macro enabled file.
Highlight the table and name the range with the name box and make sure you press enter in that box.
While it's still highlighted we are also going to change it into a table as well.
Navigate to the first of the sheets where you want the data to appear for one of your clients.
Now we are doing our query from Data toolbar, choose Get Data, From Other Sources, From Microsoft Query.
Choose New Data Source and press Ok.
Enter an appropriate name for your query perhaps the client name.  And select the Microsoft Excel Driver, with the .xlsm option. 
Choose the Connect button, in the next dialog that comes up, choose the Select Workbook button, and pick this spreadsheet from where it is saved.  Click OK. Click OK to return to the Create New Data Source popup.  Select the table name that you set up.  Press OK.

 It will return to the Choose Data Source screen with the newly created query highlighted.  Press OK.

Then the Query Wizard should display.  Your named range should appear on the left side of the window.  Choose the columns you want to move across.  By clicking on them and then clicking the arrow.  Click Next.

On the filter Data step choose to filter for the data you want to appear on that sheet. I chose the client name and then equals and then the specific client on my worksheet.  Then click next.

Set up any sorting order you want on the Client’s page.  The example I've used is Due Date.

Choose “Return Data to Microsoft Excel” and then press Finish.
Note that sometimes this next dialog can take awhile to popup, or can hide behind other screens.  You might see the Microsoft Query icon in the start bar, and you might have to click on it to get the screen up. 
In the Import Data dialog make sure you choose Table.  Then Press OK. 

As it is pasted in as a table you can refresh it at any time by clicking in it and going to the Table Design Toolbar.  Then click on the Refresh button.  Note you can choose the table format from the Table Style in the Table Design Toolbar.
Repeat the steps for each client sheet page.
Thanks for watching the video, please like or share it and subscribe to make sure you get future tips.