(Excel-based) Spreadsheet Validation

As promised, here is the example spreadsheet. It was designed for Excel 2003, but should function correctly in other versions. Note that all the passwords are for the sake of this demonstration: 'password'. In production use you would not make the passwords the same (nor so simple).

Example Spreadsheet (Excel 2003 spreadsheet)

The MD5SUM of the example spreadsheet is b08a1eee108b61c3b8512834d3cf2717

The presentation (with the links) is also available

Validating Excel-based Spreadsheets (Powerpoint)

Validating Excel-based Spreadsheets (OpenOffice)

Validating Excel-based Spreadsheets (PDF)

Useful links

I've been asked to put up the 'Active Agent' tables.

Excel is an Active Agent - results without validation but with formatting of cells
(Excel 2003)
Test Value Integer format
(and stored value)
General Format
12-3 39519 12-Mar (displayed)
12/3/2008 (stored)
12/3 4 12-Mar (displayed)
12/3/2008 (stored)
12-Mar 39519 12-Mar (displayed)
12/3/2008 (stored)
12/3/07 39153 12/03/2007
12/3/2007 (stored)
22/22/22 22/22/22 22/22/22

None of the test values were prefixed with '=' to indicate a calculation. Changing the format of the cells to (say) number also converts any existing values, thereby changing them. If you think that this action is correct (after all, 12/3 equals 4) even though it changes the original value, you might consider why 22/22/22 doesn't result in .04545454 (22 divided by 22 divided by 22). Remember, 12/3 might be a study code or some other data.

Excel is an Active Agent - results with Excel 'validation' (Data menu -> validation) settings (Excel 2003)
Test Value Integer format General Format Number format (2dp)
12-3 "error" 12-Mar (displayed)
12/3/2008 (stored)
12/3 4 12-Mar (displayed)
12/3/2008 (stored)
12-Mar "error" 12-Mar (displayed)
12/3/2008 (stored)
12/3/07 "error" 12-Mar (displayed - note this is 2007)
12/3/2007 (stored)
22/22/22 "error" 22/22/22 "error"

The test values were NOT prefixed with '=' (to indicate a calculation). Shaded (yellow) boxes are the what would be expected to be the correct output (that is, either an error box is displayed or the test value is stored and displayed correctly: only 6 out of 15 are correct.