PDA

View Full Version : Solved: Data Management In Excel - Theory/Design



mailman
08-12-2008, 07:28 AM
Hi all. I'm originally a programmer with knowledge and practice of database normalization, etc, etc.

I'm currently looking at a problem where I've got literally hundreds of interlinking Excel spreadsheets that are accessed by various levels of management. However, throughout various departments we've got people adding/removing rows/columns, etc that breaks other reports, but we obviously don't find out of course until that report is opened up and we have to hunt down what happened where, etc.

So, I'm wondering if anyone can outline or has a link to an outline on how to "Tame the Beast" as it were. Or if you have any suggestions on how to best assess the problem and determin a solution. Any and all comments are welcome, however I'm looking to those who've managed this situation before (big or small).

The jist I'm getting, is to somehow centralize and lockdown our data in a central area, but I believe our budget would only support a basic database system like Access.

Here's a link that I did find on the topic (for those searching the threads for more info like I am)
http://download.microsoft.com/download/4/1/e/41e147ab-56f2-432e-bbc5-ba5577bfa0f3/ExcelIntelligence.doc

Thanks all. I look forward to your insights... :think:

Bob Phillips
08-12-2008, 07:51 AM
If the problem is that bad, you only have one choie IMO. Get the data into a proper centralized datastore. Access is okay (although I wouldn't use it), an remember that you don't need Access, just the database part which you can read/write via ADO fro a front-end, VB or even Excel. But why bother when SQL Server Express is free?

Then control the reports through queries, Pivot Tabls/Charts etc. in other words, make their tinkering irrelevant.

mailman
08-12-2008, 09:10 AM
Ok. That's what I was thinking as well.

Say I was over exagerating a bit though, and it was say in the low hundreds of Excel files. We'll say 100 excel worksheets in total (spread across 20-30 Excel files.

In this scenario, would you (xld) or anyone else have a different solution other than a non-Access database solution?

I know for my deptartment, these numbers are more accurate, but I fear it's this way in other departments as well, and so I'm trying to get a bearing on the level of effort required to re-organize their data.

Again, any opinion big/small is appreciated.

Thx for your response btw xld

Bob Phillips
08-12-2008, 09:18 AM
I don't think the number of spreadsheets is the issue, it is data maintenance, data integrity. That is always a problem with spreadsheets. You will always be better placed IMO if you hold the data one place, and only use Excel to pull data in, analyse it, graph it etc., but not use it as a data source.