Ontariohookup - Consolidating data
Once the All References list box contains the addresses of all the source ranges, you should make sure that the Top Row and Left Column check boxes aren't selected.
(You use those boxes when you want to consolidate by category rather than by position.) The Create Links To Source Data check box should also be deselected.
For example, you can use the addition formula to find the average of those values.
If the ranges are in different workbooks, you must use book, sheet, and cell references.
If the source ranges are named, you can simply specify the ranges' names.
However, linking formulas may consume more memory than you want—especially when you're dealing with several large ranges of data.
Another way to summarize and manipulate data is by creating an Excel pivot table.
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.
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.
When you move to a different source worksheet, Excel will, by default, "suggest" the same range that you highlighted in the previous worksheet.
Therefore, if the data in each source range occupies the same cells, you don't have to highlight each range—you can simply click Add after activating the appropriate worksheet.
When you do, Excel will summarize your data using the function you specified.