[Novalug] Way OT: documenting Excel Spreadsheet
cliff at palmercs.com
Tue Nov 6 11:37:04 EST 2007
Maxwell, the task at hand is documenting what the current spreadsheet
actually does, so we can get a handle on scope. We're not ready to talk
about how to replace it until we know what the current excel file actually
> On Tue, 6 Nov 2007, Cliff Palmer wrote:
>> Our client has handed us "the mother of all spreadsheets" and asked us
>> to turn it into a web-based application with the data stored in, "GASP",
>> a database. The spreadsheet is huge and no longer meets their file-size
>> limits for email attachments, so it's obviously time to upgrade.
> My suggestion is that you start the project over from scratch, with a
> database, and do it right.
> Spreadsheets are great tools for small sets of data and small sets of
> formulas. They are often misused like this when the amount or complexity
> data and forumlas rise.
> The biggest problem you have with a spreadsheet is also its biggest
> It is easy to type in a formula like profit_value = sheetA.c40 +
> sheetb.d90 +
> sheetZ.ax40 and then trust that value.
> But formulas like this are all based on assumptions. Assumptions that
> sheetA.C40 is a total of other data, that SheetB.D90 is a calculation of
> information, etc.
> If you go to SheetA and start adding/deleting or moving rows, it may move
> total from C40 to C39, C41 or elsewhere. If you (or someone else) changes
> formula in C40 but the calculation above still uses it, your data will be
> hopelessly out of control.
> I'm completely biased on this because I am a [former] database programmer
> really believes in how well things can run in a database regardless of the
> amount of data you throw at them. Forumlas in programming languages
> on databases don't use physical locations but instead use descriptions of
> I'm also stuck in a spreadsheet hell of my own as I've constructed a very
> important and huge openoffice spreadsheet that has grown to the point
> where it
> can't be trusted anymore. I have, for example, a list of food products in
> main sheet. If I cut & paste to move them around, forumlas appear to
> which is good. But If I do something as simple and friendly as sort the
> it is unable to automatically adjust forumlas on other sheets pointing to
> those specific locations. Which means I have to start over at pointing
> forumlas to specific locations.
> It's in a spreadsheet because I didn't have the time to make a database
> when I started.. the same reason most people start huge spreadsheets.
> Maxwell Spangler
> Chapel Hill, North Carolina
More information about the Novalug