Spreadsheet Design Concepts Series
Poor cohesion is one of the very common causes of update errors. The top (visible) part gets updated, but the maintainer does not scroll down to the bottom and misses the pretty much unconnected table below. The screenshot below is taken from a live critical spreadsheet. In a worksheet a column should only really have one meaning across its whole used range. And generally a row should have the same meaning (and units) all the way along too. Mixing and matching meaning is to invite errors when the model is adapted for changes in business logic, like a new financial year, extra business unit, new reporting requirements etc.
In the image above a better solution would have been to put the summary on a new sheet. That way the 2 tables, which have a different structure, can be edited and updated easily without breaking each other.
There are other reasons to avoid summaries at the bottom but that will be covered elsewhere.
25 January 2012 - UK Excel Developer Conference - London
Products for sale:
New information about the missing FileSearch feature in Office 2007 and details of our pragmatic solution (Current price GBP 30.00)
Instant Excel worksheet protection remover and password recovery (Current price GBP 15.00)
Classic Ribbon Tab
Add Excel 97/2000/2002/2003 compatible menu structure to Excel 2007
(Current Price GBP 10.00)
Products coming soon:
(Find and control external links in Excel Workbooks)
Due by Q1 2111.
(Excel VBA based spreadsheet auditing tool)
Due before the end of 2111.