Hi,
I have read the description. Prior to opening the files, what i could sense from the description, that the functionalities you want to acquire, are quite hard and obviously some limitations because of Efficient usage of Excel from Microsoft, this condition only seems possible using VBA Scripts in the Excel. VBA can perform this task for you quite easily but again due to limitations, you'll be having issues while error handling.
If i provide my suggestion, you must get Web Application for the same. You can run that Application In your own Network or on the external network to be able to access from inside or outside of your office.
In that case, As i'll be independent of making what exactly you want, i'll be able to build it exactly along with the charts and data flow. You can import and export your data using excel but all your calculations and everything will be happening on the server. You can generate reports and provide access to anyone to any extent as your requirement.
Let's discuss it over chat. And make it happen either way. Even if you want it excel, i can give it a try, without any milestone or payment. I'll try and check if i am confident to provide you exactly as you want, i'll ask you to award me this project. Let's start it.