Thank you for posting this project and detailing it to a fair level of understanding. I can have your complete requirement encapsulated into a robust com addin for Excel.
In this approach I am going to use VSTO (Visual studio tools for office) to create a com addin that is visually appealing, robust, powerful in terms of features and flexible as well as extremely secure as the code is encapsulated and cannot be exposed or de-compliled easily. I will deploy the code as an installer file, which can be very easily installed on the required computers with a few clicks. Once installed, the addin will integrate itself with the Excel version that is installed. It will also detect the version of Excel (2003/2007/2010/2013) as well as the bitness (32/ 64 bit) and accordingly suit itself to load on the target machine.
I will completely automate the consolidation process and would even improvise it further to best suit your business logic. My application will be visually appealing as it would not be relying on the traditional VBA controls. To get a feel what I can do for you in this addin, I would request that you visit my portfolio which showcases a recent TOC addin that I developed for MS Excel.
By way of introduction, I am a Microsoft certified trainer and application automation specialist for MS Excel (MCP Id: 7531519) with over 9 years of experience delivering trainings and automation solutions to corporates.