» Consolidating Lists

The following rules allow the consolidation of Lists using Consolidate:
* The structure of the Lists must be identical.
* The headings of all rows and the leftmost columns in the Lists must contain the same topic.

The number of columns and the number of rows do not have to be identical; nor does the internal order of the text.
* The Lists must have a single label row and a single column for labels.
How Excel Consolidate Data using Consolidate technique:
* The cells in the List's data range must contain only numeric data. Excel consolidates data by identifying corresponding text crossed between the header row and the leftmost column.

To consolidate Lists:

1. Select a cell within List1, press Ctrl+Shift+* (in Excel 2003, press this or Ctrl+A) to select the List, and then press Ctrl+F3 to define the Name List1, using the Define Name dialog box.
2. Repeat step 1and define a Name for List2 and a Name for List3.
3. Select a cell in any other sheet in the workbook, and from the Data menu, select Consolidate.
4. In the Reference box, press F3.
5. In the Paste Name dialog box, select List1, click OK, and then click Add to add List1 to All references.
6. Repeat steps 4 and 5, and add List2 and List3 to All references.
7. In Use Labels in, select the Top row and Left column checkboxes, and then click OK.
Screenshot // Consolidating Lists