How to Link Excel Data over Multiple Sheets

Would you like to be able to view a master list – of client activities, or client support queries – and then have a sheet in excel for each of the clients.  You can do this with Microsoft Excel by linking the data with Microsoft Query.  The steps to follow in Excel 2007 or later are (the screenshots are from Excel Office 365):

Step 1

Create all the workbook sheets that you need – your master list and the individual client sheets.  In the Master List page put the column headings you want and a couple of lines of sample data so you can see this working.  One of the columns will need to be the client names that are the same as the sheet names you have set up.  Save your file as a Microsoft Macro enabled type (*.xlsm).

Step 2

Name the master table range by highlighting the table (the headings and the data in the rows), type a name for the range in the Name Box that is up to the left of the formula bar, then press Enter.  This is required for us to use in Microsoft Query later.

Step 3

The area should still be highlighted, now choose to create a table by going to the Insert toolbar and choose Table.  You will name it and press ok.  This might seem like you are doing something twice, but we do this so that as you add rows in the master sheet then it automatically is part of the table and feeds to the different sheets.

Step 4

Navigate to the first of the sheets where you want the data to appear for one of your clients.

Step 5

Click Data toolbar.  Click Get Data button, choose the From Microsoft Query option. Choose <New Data Source> and press Ok.

Step 6

Enter an appropriate name for your query.  Select the driver “Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)”.  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 (the example was set up as masterlist).  Press OK.

Step 7

It will return to the Choose Data Source screen with the newly created query highlighted.  With it highlighted just press OK.

Step 8

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.

Step 9

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

Step 10

Set up any sorting order you want on the Client’s page.  The example is sorted by Due Date.

Choose the “Return Data to Microsoft Excel” and then press Finish.

Step 11

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 stage 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 steps 4-11 for each client sheet page.

Be careful if you move your file to another location, then the queries may have problems.  When you first try to refresh after the move it will show that the file is missing and error but when you press OK it will bring up a dialog box for you to choose the correct spreadsheet in the new location.  If you still have problems after that in the Data toolbar, choose the Queries & Connections button, and right click on each query showing in the right of the screen and choose Properties.  Then in the definitions tab check the Connection string section to make sure the DBQ address and the DefaultDir are correct.