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

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

You might consider the option of writing a custom application to accept data input. With a custom application, you can control what the user inputs and perform data validation specific to your business rules.

If you can't afford application development, you might take a look at Microsoft's new LightSwitch project. You can quickly design a form-over-data application without writing a single line of code. Just tell it your data source, give it some validation constraints, and it builds a front-end application for data entry.

Otherwise, you might create a form in Microsoft Access. It is a little better than having users directly input into a spreadsheet.

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).

Answer 3

You could look into the PowerPivot add on for Excell 2010.
With it you can easily gather data from various different sources and
link them in various ways. the data is stored in a format
that can be imported directly into SSAS as a cube.

Because it's all still in excel your users wouldn't have to learn new UIs.

Answer 4

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.