Data gathering and manipulation: is there something better than Excel? [closed]

Question

I work with people who have historically used Excel and e-mail to 'gather' data from their external contacts. The cells these contacts populate are linked to complicated equations (occasionally macros), or are part of a large cascading cell relationship.

All the data we gather produces multiple outcomes, but all of it requires additional interrogation - intermediary workbooks to interrogate the multiple workbooks we receive from our numerous clients, lot of referencing cells (index/match, dynamic ranges), etc, etc. A large potential for error.

Therefore, I have my doubts about how good/reliable/secure Excel is when it comes to collecting data in this way. I've created small Access databases before so can see the advantages of a database, but I've never done complex calculations within them (only simple expressions).

If I was to research into the systems/technologies needed to move to better data gathering/management/usefulness environment, what would I be looking for? And could a database like Access or SQL manage complex equations, cascading field links, etc?

Many thanks for any thoughts or links.

Answer 1

I'm a database person and the thought of someone doing this isn Excel instead of a database where it belongs makes my blood run cold. However, and this is a big however, your users are clearly Excel experts and extremely comfortable with the flexibility of Excel. They will almost certainly hate a database solution (loss of flexibility, having to have a programmer do things they used to be able to do for themselves, etc.) and will almost immediately export the data to Excel any way they can. If they (or management) aren't asking for a new system, I wouldn't give them one.

Answer 2

You might consider creating a web application that connects to a database to do this. One nice thing about this is that you can separate you business and data layers, which is usually a good practice. You will also have one central location where information comes in through which should simplify your life.

Once you have a clear separation between business rules and data storage you can do most of the equations you are talking about in C# (or your language of choice) and then store the end result in your SQL Server database (or your DB of choice).