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. In the Paste Name dialog box, select List1, click OK, and then click Add to add List1 to All references. Repeat steps 4 and 5, and add List2 and List3 to All references. In Use Labels in, select the Top row and Left column checkboxes, and then click OK.But the Consolidation Assistant came to his rescue!Suzanna Creasman called and discussed here workbook consolidation task with us. She wrote back: "Thank you, I have used it and love it. Once you have the data consolidated into one workbook you can use the search features of the Consolidation Assistant to extract ranges from selected worksheets: One of our customers used the Consolidation Assistant to consolidate a survey worksheet he had sent out to over a 100 managers.The worksheet had responses scattered all over it .The following rules allow the consolidation of Lists using Consolidate: * The structure of the Lists must be identical.
Next, he used the extract feature of the Consolidation Assistant to extract values from the same cell on each sheet and place on a new worksheet, which became his database worksheet.
This is pretty easy to accomplish in Excel, so long as you take the time to ensure that your data is formatted properly ahead of time., with the same headers and types of data, and there can’t be any blank rows or columns.
When you’ve arranged your data to those specifications, create a new worksheet.
You can even draw from other workbooks by using the Browse button, otherwise known as Select in the Mac version of Excel.
Tick the box titled Create links to source data if you’re going to continue to update the data in other sheets, and want this sheet to reflect that.