Consolidating spreadsheets in excel
Step 2: Ensure the data is organized in the same way (see example below).Step 3: On the Data ribbons, select Data Tools and then Consolidate.The disadvantage: This method is very troublesome if you have to deal with several worksheets or cell ranges.On the other hand: For just a few ranges it’s probably the fastest way.The best way to find out what the others do is to play around with them, doing a few consolidations.
If you get workbooks that have identically structured data on each worksheet, you may be interested in a way to combine the multiple worksheets into a single, large worksheet.The next method comes with some disadvantages and is a little bit more complicated.It works, if your files are in a systematic file order and just want to import some certain values.The concept behind doing the condensation is rather easy: You simply need to copy the data from the second and subsequent worksheets to the first empty row on the first worksheet. End(xl Up)(2) Next End Sub is your source for cost-effective Microsoft Excel training.
Fortunately, Excel includes a feature that allows you to do this very process—the Consolidate tool. Select ' copy cells selected in the new sheet on last line Selection. This tip (3005) applies to Microsoft Excel 97, 2000, 2002, and 2003.
You build your file and cell reference with the INDIRECT formula. With the INDIRECT formula you can link to other files.