Omni-spreadsheet hints

Bookworm55

Hi,

I'm working on something at the moment and I'd like to pick your collective brains for suggestions.

I'm in the process of creating a new customer contract database thing, and while others are handling the technical side, I'm collating all the source data. The way it works is this: we have customers whose equipment testing will become due at some point in the next five years. I have sixty monthly spreadsheets detailing which contracts are up for renewal every month between January 2010 and December 2014. Each sheet is 150-250 contracts long.

All of these are being used to create one super-spreadsheet, which contains a lot of additional detail that I am finding and adding. Actually for reasons of practicality the super-spreadsheet is kept in ten parts. I also need to update each monthly spreadsheet with other detail.

However, whoever set up the monthly spreadsheets originally made a lot of mistakes. A lot of contracts are on the wrong place, or the data has been misread so I've been checking each one individually. And moving them to the right place.

This has been a long-term project with several of us doing different parts, and it's inevitable that mistakes have been made. I'm finally getting near the point when I have all the monthly spreadsheets (which will be used by the sales team directly) and all the parts of the super-spreadsheet (which will be used to create the new sales department system) ready.

What I need to do is check that each contract on the sixty small spreadsheets is represented exactly once on the ten-part super spreadsheet. I strongly suspect there have been duplicates where a contract has been moved across monthly sheets. Or even not added at all depending on when it was moved. This is made more difficult by the fact that several customers have multiple contracts (for different sites, etc).

What I need to to is one massive reconciliation between the two. Any suggestions on how to go about it?

blobbyh

It's hard to picture other people's spreadsheets without seeing them and while I'm sure there are valid reasons for creating such monster sheets it does sound like overkill to me.

How often do contracts renew? If only once a year, couldn't you enter each renewal date as a date value, have sixty monthly columns across with the first and last days of each month also set as date values, so each renewal date would slot into the correct month/column?

Going slightly off track, but my Excel cashflow forecaster is date column driven with each day value slotted into 243 days, summarised into weeks, then into months. I use a date in cell A1 which moves all the dates forward by seven days each time, tells me the cash position for each with various error checkers to tell me when things have gone bad. At the end of it I get a final cash number with which I show a nice graph to the MD.

Bookworm55

The contracts renew once a year, once every three years or once every five years.

Each contract takes up a single row, and there are forty-four columns of data on the monthly expiries sheets. This is compared to one hundred and twenty-six columns on what is going to be the master spreadsheet. When it's finished, the super-spreadsheet will be 126 columns wide by 12,000 rows deep. In case you were wondering, yes the 44 on the monthly sheet is a subset of the 126 on the master.