Lots of staff, lots of work... lots of spreadsheets. This does not need to be a mess. Excel VBA to consolidate reporting can provide good programmatic controls and on-demand updates. All with a single click.
Using excel to track the progress of projects or business units or client deliverables is not unusual. Excel is a powerful, versatile, and relatively cheap tool understood by many. Used properly it has good controls especially if linked to databases or other applications.
The reality for many businesses though is that Excel is only used reasonably well.
It's not unusual to see dozens or hundreds of different Excel Workbooks. One for each major client or project or whatever. Understanding the status of all of these workbooks becomes a major challenge and performing any type of consolidated analysis or reporting can be next to impossible.
I've seen Finance, Sales, and Marketing departments proudly show linked spreadsheets and intricate processes to generate their monthly status and metrics from a myriad of spreadsheets. Unfortunately, this is typically a huge time-killer and prone to error. Not to mention that any reporting is out of date by the time it's complete.
A solution that uses Excel VBA to Consolidate Reporting Automatically from Multiple Workbooks Exists.
Before I run through a quick example of using Excel VBA to Consolidate Reporting it's important to note that other (better) solutions also exist. Transitioning well-understood and relatively standardized processes from Excel to good quality Applications is an obvious answer. It's something I strongly encourage. It's also something I'm realistic about. Limited resources, competing priorities, processes that are still evolving, time to install, configure and develop (not to mention cost) all mean that Excel-based solutions often stay longer than they should. Given this reality, unlocking the true power of Excel is well worth it.
So let's look at an example.
Excel VBA to Consolidate Reporting - Worked Example
This example builds on the workbooks generated from the VBA to Dynamically Create Excel Workbooks post. In short, the example assumes that dozens of complex Excel workbooks are used to manage the hundreds of work steps within each client project.
Here is a short video of the Reporting App followed by some screenshots:
For the purposes of this worked example, I've only used a handful of project files from a couple of different locations.
1. The Reporting App allows the user to select project files and see them listed.
2. The user can 'Check' the files and grab summary information. To demonstrate what is possible I've included static information from each file and also calculated total work steps and the % completed.
3. With more sophisticated code any type of reporting is possible.
For example, let's say that management wants to understand the status and performance of a specific work step across all projects.
To demonstrate this I allow the user to select a specific work step and then output a record for that work step for every location/version combination from every workbook. By including the original, forecast, and actual dates users can see the overall performance, current status and identify issues.
For this to work efficiently and for reporting flexibility I create an Item class and treat each Project/Location/Version combination as an object. This will be meaningful to people familiar with object-oriented programming and hints at what is possible.
Here is the example output:
Conclusion
The above demonstrates that reporting from multiple workbooks is possible without the need for links or any manual intervention. Further automation such as generating visualizations, exception reporting, or pushing data into databases is also possible.
The key is understanding the business process and the value of the information being managed. Grabbing this information quickly, easily, and producing meaningful reporting automatically... rapidly unlocks value.
Awesome. Thanks for sharing.