Consolidating data in excel workbooks
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.
For instance, you have three sheets data as below screenshots shown, now you want to consolidate the average, max or min value of each product in each month, how can you do?
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.
For example you have 100 worksheets of data and all the worksheets has the similar structure. I like to solve and automate any possible opportunity in Excel including in combination with Office Applications (Access, Outlook, Word, Power Point, Visio, etc) and other Non-Office Applications (SAP, IE, Batch, Configuration Management Tools, etc).
All we would want is getting all the data multiple worksheets consolidated into a single worksheet. Note: All the solutions given here are published after thoroughly tested to my knowledge, however it is advised to keep a copy before you try it.The most terrible thing is that the workbooks you need to combine contain multiple worksheets.