We know that our spreadsheets should be functional and error free, but there is a tension between how we review a spreadsheet to ensure that it is correct and the functionality of the spreadsheet itself. None of us like Big Brother breathing down our necks too closely.
In What is a Cell Map? I discussed the creation of a simplified version of a spreadsheet to aid review and error trapping. However, the map is itself a spreadsheet. If you created maps for each worksheet in your workbook, the workbook would quickly bloat and become unmanageable. The obvious answer is to post your maps somewhere else, but clearly linked to primary workbook.
The ACBA-Mapping package does just that. It generates a separate Map Book to hold all the spreadsheet maps (and other analyses) associated with the parent workbook. The parent workbook has a new worksheet that maintains a summary of all the detailed analyses generated for the workbook. This new worksheet is called 'DataList_and_MappingControl' and is, effectively, a reserved worksheet name.
The 'Source' column lists analyses that have been applied to the whole book or an individual worksheet. Note that not all the worksheets are visible, but they will still be reviewed if you apply an analysis to the whole workbook. Most importantly this worksheet has a hyperlink, called 'Mapping Index', to the associated Map Book. Mapping Index refers to all the maps and other analyses you have created for the parent workbook.
Following the Mapping Index hyperlink opens the associated map book at its indexed worksheet.
In What is a Cell Map? I discussed the creation of a simplified version of a spreadsheet to aid review and error trapping. However, the map is itself a spreadsheet. If you created maps for each worksheet in your workbook, the workbook would quickly bloat and become unmanageable. The obvious answer is to post your maps somewhere else, but clearly linked to primary workbook.
The ACBA-Mapping package does just that. It generates a separate Map Book to hold all the spreadsheet maps (and other analyses) associated with the parent workbook. The parent workbook has a new worksheet that maintains a summary of all the detailed analyses generated for the workbook. This new worksheet is called 'DataList_and_MappingControl' and is, effectively, a reserved worksheet name.
 |
| A worksheet like this is added to the Parent Workbook |
Following the Mapping Index hyperlink opens the associated map book at its indexed worksheet.
 |
| The Index Sheet lists all the analyses that have been undertaken on the parent workbook. |
Generating these Control Sheets
The ACBA-Mapping software is controlled through the Add-Ins menu.
 |
| ACBA Functions menu highlighting the Mapping/ Control Records |
Before mapping a worksheet (or any other analysis) the user must create the Control Records. This is an automatic process but can take a while for workbooks with several worksheets.
Access to the Software
The software package is available, without charge, from the link ACBA Mapping. If you need advice or have any queries please contact me at steveallen@netcom.co.uk.
Spreadsheet errors and inconsistensies is a subject that is of concern by professional developers as well as amateurs. The European Spreadsheet Risk Interest Group (EuSpRIG) considers the problems from an academic and professional perspective. The following links provide access to the EuSpRIG website and discussion forum.
Comments
Post a Comment