Consolidating

If you have a group of tables that exist in different workbooks but contain similar data it is possible to combine (or consolidate) all this data into one table in one workbook.
Before you follow this example it is recommended that you are familiar with how to consolidate worksheets first. This is explained in detail on the previous page.
All the corresponding workbooks must be saved before you can consolidate them.

Consolidating 2 Workbooks

Lets assume that you have two workbooks, for example lets consider the following two workbooks.
We are also going to assume that these worksheets exists in different workbooks.
In this example we are going to use consolidation by category (as opposed to position).
You cannot use consolidation by position since the data is not identical and what you actually want to end up with is the totals for each Region on a monthly basis.
The worksheets must have the same column headings, but can have different row headings.

These workbooks have been saved in the folder "C:\Temp" although they can actually be saved anywhere.
The only workbook that actually needs to be open is the one that will contain the consolidated data.
And for this we will create a new blank workbook.

Creating a new workbook

If possible move all the workbooks you want to consolidate to the same folder, as this will make it easier to consolidate them.
Then create a new blank workbook and save this.
Create a row of headings that are identical to the columns in the worksheets you want to consolidate.
Select the first cell in the range, i.e. "B3" and then select (Data > Consolidate) to display the Consolidate dialog box.

Adding the References

You can use the Browse button to browse to the first workbook you want to include.
After you have found the workbook the following will appear in the Reference box.

There is currently no way of it knowing which worksheet you actually want to refer to so you will have to edit the reference manually.
Don't Bother though as it does not seem to work !!.
The only way I got it to work is to have each workbook open and select the exact range with the mouse.
If the workbooks are open you can use the Window menu to allow you to display each one so you can select the relevant worksheet and range of cells using the mouse.
Not sure how you would get round this if you had a lot of workbooks to consolidate ?
Press OK to consolidate the workbooks and to create the consolidated table.
Make sure the "Left column" checkbox is checked.

A separate line is created for every unique item in the left most column.

Linking your Data

It is possible to create an automatic link by selecting the "create links to source data" checkbox.
When you create with links, an Outline will be created automatically in the consolidated data table.
Each item is linked separately.

Important

All the references you add to the Consolidate dialog box are saved when the workbook is saved. This makes it very easy to update the data even when you do not link to the source data. Just press (Data > Consolidate) and press OK.
When you are updating your consolidated range always highlight the whole table and not just a few cells. You will either get some of your data updating or none of it.