[Novalug] Way OT: documenting Excel Spreadsheet

[Palmer]

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
does.
Thanks
Cliff

> 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
> of
> data and forumlas rise.
>
> The biggest problem you have with a spreadsheet is also its biggest
> strength:
> 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
> tax
> information, etc.
>
> If you go to SheetA and start adding/deleting or moving rows, it may move
> the
> total from C40 to C39, C41 or elsewhere.  If you (or someone else) changes
> the
> 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
> that
> 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
> operating
> on databases don't use physical locations but instead use descriptions of
> the
> data.
>
> 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
> the
> main sheet.  If I cut & paste to move them around, forumlas appear to
> adjust,
> which is good. But If I do something as simple and friendly as sort the
> data,
> 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
> app
> when I started.. the same reason most people start huge spreadsheets.
>
> --
> --
> ----------------------------------------------------------------------------
> Maxwell Spangler
> Chapel Hill, North Carolina
>