Macro for consolidating excel files
Since the source ranges are the same size, you don't have to specifically select the entire destination range. command from the Data menu to open the dialog box shown in Figure B. Once you've selected the statistic you want Excel to calculate, you should specify the source ranges.
In the Function list box, choose the summary function you want to use. You specify individual source ranges in the Reference text box and then click the Add button to place the reference in the All References list box.
(By default, this box will contain the addresses of the ranges you specified the last time you used the Consolidate... To remove those addresses, select them and then click the Delete button.) The form of the references depends on the locations of the source ranges.
If the ranges are in the same worksheet, you can simply specify the cell addresses.
Once you've specified the first source range, you should specify the others in the same way—by highlighting them and clicking Add.
Don't bother including a source range's category labels when you specify the range—Excel won't include the labels in the destination.
Excel offers a number of ways to summarize information in different worksheets and workbooks.
For example, you can use the addition formula to find the average of those values.
Consolidating by position involves a few easy steps: First, you click on the upper-left cell of the range in which you want Excel to place the summary information (we'll call this range the range). However, you can choose from a variety of other statistics (Count, Average, Max, Min, Product, and so forth) if you want.
In this article, we'll show you the simplest way to use the Consolidate... In a future article, we'll demonstrate a more complex use of the Consolidate... Consolidating by position Creating links to the source data Specifying multiple-cell destination ranges Conclusion You can consolidate by position if all the source ranges (the ranges containing the data you want to consolidate) are the same size and the data is arranged in the same order.
The ranges can exist in the same worksheet, in different sheets in the same workbook, or in different workbooks.
Before you start: if your worksheets are identical, it's probably easier to create 3D-references (if you have one workbook) or External References (if you have multiple workbooks) to consolidate your data. However, the beauty of the Consolidate feature is that it can easily sum, count, average, etc this data by looking at the labels.
Because our worksheets are not identical, we want Excel to sum cells that have the same labels.