{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Guide","Colours","Preferences","Control","Reports","Temp"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":56,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Guide","index":0,"n_rows":608,"n_cols":2,"rows_kept":608,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Rainbow Analyst User Guide"]
[]
[null,"User Guide Contents"]
[]
[null,"If you have turned off in-cell editing, you can double-click any of the items immediately below to go to the relevant heading."]
[]
[null,"Row     Heading"]
[null,{"$f":"TEXT(ROW($B$39),\"000\") & \"      \"&$B$39","v":"039      Quick Start Guide"}]
[null,{"$f":"TEXT(ROW($B$66),\"000\") & \"      \"&$B$66","v":"066      Overview of Rainbow functions"}]
[null,{"$f":"TEXT(ROW($B$104),\"000\") & \"      \"&$B$104","v":"104      Using Rainbow in practice"}]
[null,{"$f":"TEXT(ROW($B$120),\"000\") & \"      \"&$B$120","v":"120      Helpful hints"}]
[null,{"$f":"TEXT(ROW($B$136),\"000\") & \"      \"&$B$136","v":"136      Rainbow functions in detail"}]
[null,{"$f":" TEXT(ROW($B$140),\"000\") & \"            \"&$B$140","v":"140            Quick Clean-Up"}]
[null,{"$f":" TEXT(ROW($B$146),\"000\") & \"            \"&$B$146","v":"146            Delete Comments"}]
[null,{"$f":" TEXT(ROW($B$152),\"000\") & \"            \"&$B$152","v":"152            Reset Reports"}]
[null,{"$f":" TEXT(ROW($B$160),\"000\") & \"            \"&$B$160","v":"160            Save Format"}]
[null,{"$f":" TEXT(ROW($B$168),\"000\") & \"            \"&$B$168","v":"168            Undo Changes"}]
[null,{"$f":"TEXT(ROW($B$174),\"000\") & \"            \"&$B$174","v":"174            Summary Report"}]
[null,{"$f":"TEXT(ROW($B$192),\"000\") & \"            \"&$B$192","v":"192            Dependency Map"}]
[null,{"$f":"TEXT(ROW($B$216),\"000\") & \"            \"&$B$216","v":"216            Inter-Sheet Logic"}]
[null,{"$f":"TEXT(ROW($B$234),\"000\") & \"            \"&$B$234","v":"234            Map Risks"}]
[null,{"$f":"TEXT(ROW($B$354),\"000\") & \"            \"&$B$354","v":"354            Report Objects"}]
[null,{"$f":" TEXT(ROW($B$366),\"000\") & \"            \"&$B$366","v":"366            Manage Names"}]
[null,{"$f":" TEXT(ROW($B$396),\"000\") & \"            \"&$B$396","v":"396            Sample Data"}]
[null,{"$f":"TEXT(ROW($B$408),\"000\") & \"            \"&$B$408","v":"408            Formula Groups"}]
[null,{"$f":"TEXT(ROW($B$426),\"000\") & \"            \"&$B$426","v":"426            Cell Inspector"}]
[null,{"$f":"TEXT(ROW($B$440),\"000\") & \"            \"&$B$440","v":"440            Compare Worksheets"}]
[null,{"$f":"TEXT(ROW($B$481),\"000\") & \"            \"&$B$481","v":"481            Find Colours"}]
[null,{"$f":" TEXT(ROW($B$489),\"000\") & \"            \"&$B$489","v":"489            Colour Chart"}]
[null,{"$f":"TEXT(ROW($B$503),\"000\") & \"            \"&$B$503","v":"503            Format Editor"}]
[null,{"$f":" TEXT(ROW($B$523),\"000\") & \"            \"&$B$523","v":"523            (Un)Hide/Protect"}]
[null,{"$f":" TEXT(ROW($B$533),\"000\") & \"            \"&$B$533","v":"533            Preferences"}]
[null,{"$f":" TEXT(ROW($B$576),\"000\") & \"            \"&$B$576","v":"576            User Guide"}]
[null,{"$f":" TEXT(ROW($B$580),\"000\") & \"            \"&$B$580","v":"580            Rainbow Log"}]
[null,{"$f":"TEXT(ROW($B$590),\"000\") & \"            \"&$B$590","v":"590            About Rainbow"}]
[null,{"$f":"TEXT(ROW($B$596),\"000\") & \"      \"&$B$596","v":"596      Installation and licensing"}]
[]
[null,"Quick Start Guide"]
[]
[null,"If you're someone who likes to get straight on with using a product, this \"Quick Start\" section tells you the key things you need to know to get the best out of Rainbow Analyst."]
[]
[null,"When you click a button on the Rainbow tab in the Ribbon, you'll generally see a dialog box with \"Click OK to …\" near the top. This is Rainbow's guess as to what you want to do, based on your currently selected workbook and worksheet(s)."]
[]
[null,"To change the selected workbook and worksheet(s), click the Selection… button. This will open a box where you can select a different (currently open) workbook and worksheets. Hold down the Shift key to select a range of worksheets, or the Control key to select or deselect individual sheets, or click the Select All button."]
[]
[null,"Most Rainbow Analyst functions perform one or more of the following actions:"]
[null,"•  apply colour to specific cells in the worksheet(s) you have selected;"]
[null,"•  generate worksheet map(s) in which specific cells have been coloured to reflect information about the corresponding cells in the worksheet(s) you have selected;"]
[null,"•  generate a worksheet report listing specific cells or other items."]
[]
[null,"You can select which of these actions you want in the Output area of the dialog box (usually under the Selection… button). Some options may not always be enabled (i.e. you can't click on them). For instance if the Report option box is ticked but it is not enabled, this means that the Rainbow Analyst function you have selected always generates a worksheet report (i.e. the report can't be turned off)."]
[]
[null,"Many Rainbow Analyst reports now include a hyperlink on each row (usually in the \"Cell\" column) which you can click in order to jump straight to the relevant cell in the workbook you are analysing. To make this easier, Rainbow turns off the warning message that Excel normally displays when a hyperlink is clicked, but if you wish you can set your Preferences (under the Reports tab) to turn this message back on ."]
[]
[null,"If you want Rainbow Analyst to apply colour to your selected worksheet(s), but you want to be able to restore the original colouring afterwards, then you first need to use the Save Format function (in the File Control group). This will save the current formatting of your selected worksheet(s), so that you can restore it afterwards using the Undo Changes function."]
[]
[null,"If you need to stop Rainbow in the middle of running a function for any reason, simply press Esc or Ctrl+Break. Rainbow will stop and will generally let you know which cell it reached in running the analysis. Of course this means that if Rainbow was producing a report, the report will probably be incomplete."]
[]
[null,"As with all the tabs on the Excel \"Ribbon\" (menu), there are keyboard shortcuts for the Rainbow tab and all the Rainbow functions; you can see them by pressing and releasing the Alt key. So for instance the quickest way to run the Find Colours function is to press Alt, then Y (for Rainbow), then F (for Find Colours)."]
[]
[null,"That's all you need to know to get started. For full information on how to use Rainbow's various functions, please read the rest of the User Guide (accessible as a worksheet from the User Guide button). To obtain a printed copy of the Guide, simply print it like a normal spreadsheet."]
[]
[null,"Please consult the final section of the User Guide for full information on installing Rainbow Analyst, and also for important information on copyright and liability."]
[]
[null,"Overview of Rainbow functions"]
[]
[null,"The Rainbow tab has five groups of functions: File Control, Structural Overview, Risk Analysis, Formula Checking, and Other Tools. Within each group, the first function is generally the most important; if you were to use only three of Rainbow's functions, they should probably be the Summary Report, Map Risks and Formula Groups."]
[]
[null,"The first set of functions on the Rainbow tab (in the File Control group) can help you get set up before you start using Rainbow's analysis tools:"]
[null,"•  The functions on the File Clean-Up menu let you quickly clean up a workbook, delete any comments, and then save it as a working copy."]
[null,"•  Reset Reports lets you tell Rainbow that you want it to put reports in a different workbook from the one it is currently using."]
[null,"•  Save Format records the current formatting of your selected worksheet(s), so that you can restore it again using Undo Changes;"]
[null,"•  Undo Changes lets you restore cell colours and other selected aspects of cell formatting from the last format that you saved."]
[]
[null,"The next three items on the Rainbow tab (in the Structural Overview group) are powerful \"floodlight\" functions, which are probably the first ones you'll want to use when starting to analyse a spreadsheet:"]
[null,"•  Summary Report gives you a quick overview of a workbook, and lists data for each sheet which can already start to suggest possible problem areas."]
[null,"•  Dependency Map shows you the role of each cell in the logic of the spreadsheet, and highlights potential issues such as blank input cells."]
[null,"• Inter-Sheet Logic provides a fast analysis of logic flows between sheets, with a matrix-style report that lets you drill down for detailed information."]
[]
[null,"The next group of items (the Risk Analysis group) consists mainly of \"spotlight\" functions which zoom in to examine specific details:"]
[null,"•  Map Risks is a powerful analysis tool which can report on up to 28 different types of potential risks, and highlight all the relevant cells in different colours."]
[null,"•  Report Objects lets you generate detailed reports on links to other workbooks, and also on comments (cell notes), pivot and data tables, and buttons and graphics."]
[null,"•  Manage Names generates an interactive report on defined names, and lets you convert names to cell references and delete invalid names."]
[null,"•  Sample Data selects a random or periodic sample of data from a worksheet and copies it to a report for further analysis."]
[]
[null,"The next three items (in the Formula Checking group) are designed to help you move on to a detailed audit of the calculations in a spreadsheet:"]
[null,"•  Formula Groups highlights (and reports on) groups of copied formulas, so that you can easily spot inconsistencies and adjustments."]
[null,"•  Cell Inspector is a powerful interactive function which lets you trace the logic of any individual cell at a glance, and is very useful in conjunction with Formula Groups."]
[null,"•  Compare Worksheets is a comprehensive comparison function (comparing workbooks and ranges as well as worksheets), and it provides a key tool for control of versions and scenarios."]
[]
[null,"In the Other Tools group are some utility functions to manage Rainbow's colours and assist with spreadsheet development:"]
[null,"•  Find Colours gives you an explanation of the Rainbow colour(s) in the selected cell, and lets you search for other similarly-coloured cells."]
[null,"•  Colour Chart gives you an interactive visual display of all the colours that Rainbow uses to map different types of cells and different groups of formulas."]
[null,"•  Format Editor works in conjunction with other Rainbow functions to enable you to apply formatting changes or convert text to numbers."]
[null,"•  (Un)Hide/Protect can hide or unhide and protect or unprotect selected worksheets, and also unhide hidden rows and columns."]
[null,"•  Preferences lets you set various options, for instance how Rainbow should decide what workbook to use for its reports."]
[]
[null,"The final item on the Rainbow tab is the User Guide (Help) menu:"]
[null,"•  User Guide displays this User Guide in the form of a new Excel workbook that you can view, save or print."]
[null,"•  Rainbow Log lets you view the Rainbow log file, with details such as items recently unhidden or unprotected, potential risks found, and text cells converted."]
[null,"•  About Rainbow enables you to register your copy of Rainbow Analyst, and displays licensing and support information."]
[]
[null,"Using Rainbow in practice"]
[]
[null,"Before using Rainbow's analysis functions, you may find it helpful to use the the functions on the File Clean-Up menu. The Quick Clean-Up function unhides any hidden sheets, rows and columns, unprotects protected sheets, and clears existing cell colours to avoid confusion with Rainbow's own highlighting colours. File Save As allows you to save the cleaned-up workbook under a different name as your working copy. (Delete Comments is mainly useful later after running Map Risks.)"]
[]
[null,"When you first start to look at a spreadsheet developed by someone else, you may want to begin by understanding the overall structure and logical flow. For this purpose it is always worth running the Summary Report, which will give you headline information and highlight possible problem areas. The Dependency Map function can also quickly highlight structural problems (see next paragraph), and the Inter-Sheet Logic function will give you an overview of the logic flows between worksheets."]
[]
[null,"With the help of the Dependency Map function you will probably be able to spot some errors simply by looking at the layout of the various dependency types. For instance, if you find some input cells sitting on a row without any label, this may well indicate a formula with an incorrect reference. (You can find the dependent formula by using the Cell Inspector function.) And if Rainbow shows you that some values which are supposed to be inputs are actually just labels (i.e. have no dependents), this clearly indicates a part of the spreadsheet which is not working correctly."]
[]
[null,"Before you start looking in detail at the calculation logic in the spreadsheet, you will probably find it helpful to use Map Risks and the other functions in the Risk Analysis group. The default settings for Map Risks will give you a very helpful report on omitted precedents, blank inputs, error values, hard-coded formulas, complex formulas, numbers formatted as text, and cells with hidden text. And the other functions in this group can tell you about aspects such as external links and defined names."]
[]
[null,"The Formula Groups function can also be very useful at this stage, as well as later on for detailed formula checking. It provides a quick visual indication of inconsistencies in the way formulas have been copied and used, and (as with the dependency map) you will probably be able to spot some errors just by looking at the visual layout of the formula groups. In particular, you will be able to see where individual formulas have been overwritten or modified inconsistently, perhaps to make last-minute adjustments."]
[]
[null,"Eventually you will need to check (some of) the detailed calculations in a model, and at this stage you will need to use the Formula Groups function together with the Cell Inspector. The Formula Groups function will produce a report listing all the blocks of copied formulas in a spreadsheet, so that you can save a great deal of time by checking just one formula in each block. You can use the Cell Inspector to help with this detailed checking, by showing you the precedents and dependents of any particular cell, as well as an indication of what they mean (e.g. Net Profit - Year 1). And it will even help you analyse the structure of complex formulas."]
[]
[null,"As well as auditing spreadsheets developed by others, Rainbow can also help you in developing your own spreadsheets. For instance, you can select Dependencies (under Map Risks) and then Non-blank input cells to colour all (non-blank) input cells, and you can then select Format Editor and Turn off cell locking to set all these input cells as unlocked. Finally you can use (Un)Hide/Protect to turn on protection for all sheets (and the workbook), and you then have a workbook which complies with best practice by allowing users to change only the input cells, and protecting the rest of the spreadsheet."]
[]
[null,"Helpful hints"]
[]
[null,"As explained in the first section of this Guide, the dialog boxes for most Rainbow functions are basically similar, and (by clicking the Selection... button) they let you select which workbook and worksheets to analyse. Note that if your selected workbook contains hidden or protected sheets, in most cases Rainbow will prompt you to unhide or unprotect them before using Rainbow functions (as otherwise most Rainbow functions may not operate correctly). And Rainbow may select cell A1 for you if your current selection is something other than a normal range (cell), e.g. a chart."]
[]
[null,"The default Preferences settings are to include all Rainbow reports in one report workbook, and for Rainbow to look for currently open report workbooks. So if you want to use a workbook from a previous session, make sure it is already open in Excel when you start using Rainbow. Alternatively you can make use of this feature to append reports as additional worksheets at the back of the workbook you are analysing."]
[]
[null,"Nearly all Rainbow reports (except the Summary Report and Inter-Sheet Logic) are formatted as data tables. This means that you can easily sort them to highlight certain values, or filter them so that for instance they show only results for a specific sheet. And you can change the formatting style of the table using the Styles group on the Home tab in Excel."]
[]
[null,"If you have selected output in the form of a map, Rainbow will include the map worksheet(s) as well as the report in your selected report workbook. As far as possible it will give them the same names as the worksheets they are mapping."]
[]
[null,"Normally Rainbow will look at all cells within the used range of a worksheet. However, if you have selected a range containing more than one cell, it will give you the option to analyse only the selected range. If you have selected one or more columns or rows, it will analyse the column(s) or row(s) within the used range of the worksheet. This can save a great deal of processing time in a large worksheet, if you do not need to analyse every cell."]
[]
[null,"Although Rainbow has been designed to perform quickly and efficiently, in a very large workbook some functions may take many minutes to complete. In this situation the application title (at the very top of the screen) may show \"Not Responding\", or the working area of the screen may go blank. This does not generally indicate a problem; you can confirm that Rainbow is still working away by looking at the progress message in the status bar (near the bottom of the screen). If you do need to interrupt it, just press Esc or Ctrl+Break."]
[]
[null,"If you need help while using a Rainbow function, all functions which generate a dialog box include a Help button which displays the relevant section of the User Guide, and which can also show this Quick Start Guide."]
[]
[null,"Rainbow functions in detail"]
[]
[null,"Options which are specific to particular functions are explained in detail below."]
[]
[null,"Quick Clean-Up"]
[]
[null,"The Quick Clean-Up function provides a quick and simple way to prepare a workbook for detailed analysis using Rainbow's other functions. It combines various operations from (Un)Hide/Protect and Format Editor to unhide hidden sheets, unprotect protected sheets, unhide hidden rows and columns, and clear cell colours and patterns. It also selects Normal view (rather than e.g. Page Break Preview) for all sheets."]
[]
[null,"After you have \"cleaned up\" a workbook with the Quick Clean-Up function, you can use the File Save As function to save it under a different name and leave the original file unchanged. You can also use the File Save As function to save the current report workbook under a different name, and Rainbow will then continue to use this for further reports."]
[]
[null,"Delete Comments"]
[]
[null,"With the Delete Comments function you can quickly remove all comments (cell notes) from a workbook. This is particularly useful if you are using the Insert Comments option with the Map Risks function."]
[]
[null,"Before you use Delete Comments to remove any existing comments, you might first wish to list them with the Report Objects function (select option 2). And after you have used Map Risks with the Insert Comments option, if you decide you no longer need the Map Risks comments, you can simply use Delete Comments again to remove them."]
[]
[null,"Reset Reports"]
[]
[null,"Reset Reports tells you the file name of Rainbow's current report workbook, i.e. the workbook that Rainbow is using to hold reports that it generates, and it lets you clear Rainbow's record of this (without deleting the report workbook itself). This means that when you next request a report, Rainbow will have to select a new report workbook, which can be either a currently open workbook, or a new workbook that Rainbow can set up for this purpose."]
[]
[null,"If you have chosen one of the first three options (in Preferences under the Reports tab) to enable Rainbow to select a new report workbook automatically, then the Reset Reports function will let you decide whether next time you want to select the new report workbook manually yourself, or whether you want Rainbow to make the selection automatically. This only applies to the next time you run a report; after that Rainbow will go back to automatic selection based on your Preferences setting."]
[]
[null,"Whenever a new workbook is created for reports, Rainbow will initially set this up with Excel's default new workbook name (e.g. Book1). You can then use the File Save As function to save the workbook under a more helpful name, and Rainbow will record the new name and continue to use the workbook for further reports."]
[]
[null,"Save Format"]
[]
[null,"Before you change the formatting of a spreadsheet with the functions described in later sections of this Guide, you can use the Save Format function to set a format restore point, in other words to take a snapshot of the formatting for selected worksheets, so that you can later undo the changes made by Rainbow. You can then use the Undo Changes function to restore the relevant formatting (e.g. cell colours) back to the way it was the last time you set a format restore point."]
[]
[null,"Rainbow only holds one format restore point at a time, so it will restore only the last set of formatting information that you recorded. This means that if you set a restore point before you use e.g. the Dependency Map function, Rainbow will be able to undo any formatting changes made by this function, but it will not undo any previous changes. Any saved formatting information is effectively lost when you close Microsoft Excel."]
[]
[null,"If some formatting information has already been saved, this will be overwritten when you set a new restore point; Rainbow will tell you what workbook and sheet(s) have been previously saved, and will ask you to confirm before overwriting this information."]
[]
[null,"Undo Changes"]
[]
[null,"The Undo Changes function allows you to restore aspects of the format that you have changed using the various functions described in other sections. So for instance if you set a format restore point (with Save Format) immediately after running the Quick Clean-Up function, and you then use Map Risks to highlight various cells, you can click Undo Changes to restore the \"clean\" formatting as it was after the Quick Clean-Up."]
[]
[null,"In case you are not sure what information you recorded the last time you used Save Format to set a restore point, Rainbow will alert you if it has no formatting information saved for some or all of the worksheets you have selected to restore."]
[]
[null,"Summary Report"]
[]
[null,"It is always worth producing a Summary Report before looking at a spreadsheet in detail. The report is quick to produce, and can indicate unexpected issues such as Visual Basic code, hidden sheets or excessively long formulas. The report will run even if some sheets are protected, but it can only show a very limited amount of data for protected sheets."]
[]
[null,"Note that Rainbow Analyst will be unable to access information on Visual Basic code unless you have adjusted your Macro Security settings. To do this, click the Office Button in Excel 2007 or the File tab in Excel 2010 or 2013, then Excel Options, Trust Center, Trust Center Settings, Macro Settings, then click the check box under Developer Macro Settings labelled \"Trust access to the VBA project object model\". If you have not done this, rows 22 and 23 of the Summary Report will indicate \"Unable to report\"."]
[]
[null,"The main section of the report (listing data for each sheet) is formatted like a data table (with alternating stripes) for ease of reading, even though it is not actually a table. The colour of the stripes is taken from the cell groups highlighting colour for Group 64 (the last group). Click the Colour Chart button and scroll down to find and change this."]
[]
[null,"Most of the data items in the Summary Report are self-explanatory. A large number of empty rows and columns can indicate problems with the structure of the worksheet, such as an isolated calculation situated a long way out from the main contents of the sheet. Similarly a low average density (proportion of non-blank cells) can indicate a highly fragmented structure."]
[]
[null,"The proportions of constant numbers and calculated numbers will indicate whether a sheet is used mainly for inputs or for calculations. The average and maximum formula length will point to sheets which may need particularly careful examination later, as also will the numbers of error values and comments (cell notes). If you want to see the longest formula in a particular sheet, click on the value in the Max fmla length column and the hyperlink will take you directly to the relevant formula."]
[]
[null,"By default the Summary Report will highlight in yellow or red some values which may indicate potential problems. It uses the values shown under the Criteria tab of the Preferences settings as the thresholds for highlighting large sheets and large numbers of comments, and the Complex formulas characters value as the threshold for formula lengths. If you prefer the report without highlighting, you can turn this off under the Other Functions tab of the Preferences settings."]
[]
[null,"Other Rainbow functions can provide much more detailed analysis of many of the items in the Summary Report. By default, the Summary Report includes comments (cell notes) which explain exactly where in Rainbow you can find this additional analysis. However, you can turn off this feature under the Other Functions tab within Preferences. "]
[]
[null,"Note that the Summary Report, the Report Objects report on external links, the Manage Names option to convert names, and the (Un)hide/Protect function, are the only Rainbow functions which operate on chart sheets as well as worksheets. All the other functions operate only on worksheets."]
[]
[null,"Dependency Map"]
[]
[null,"All non-empty cells in a spreadsheet can be placed in one of four categories according to whether they have any precedents (cells on which they depend for their calculations) and/or dependents (cells which depend on them):"]
[null,"•  Inputs are cells which have dependents but no precedents."]
[null,"•  Workings are cells which have both precedents and dependents."]
[null,"•  Outputs are cells which have precedents but no dependents."]
[null,"•  Labels are non-empty cells which have neither precedents nor dependents."]
[]
[null,"You can use the Dependency Map function to get a better understanding of the structure of each worksheet and to look for indications of potential problems. This function in effect creates a colour map of the logical characteristics of a worksheet, and you will see immediately whether the sheet is well structured, with consistent blocks of colour running right across rows and very few row differences. A badly structured worksheet, on the other hand, will look like a messy patchwork of different colours."]
[]
[null,"Because this function calls on Excel to check the dependents and precedents of nearly every cell, it runs more slowly than some other Rainbow functions. If you need to check a large workbook, you may decide to select all sheets and then set Rainbow running while you continue with other work. Alternatively you may be able to save time by checking only a few columns or rows. You can also set the threshold (under the Criteria tab in Preferences)  for Rainbow to skip processing dependencies when it is taking too long."]
[]
[null,"You may want to select View, Zoom and e.g. 50%, to get a quick high-level view of the spreadsheet. Generally a well-constructed spreadsheet will have few if any row differences, and the dependency types also should be consistent across rows. Areas of the spreadsheet which are labelled as being Inputs or Assumptions should obviously contain mainly input cells, and there should be no un-used \"inputs\", which Rainbow will detect as labels in cells which are marked as Inputs or Assumptions."]
[]
[null,"Similarly there should be no input cells without a descriptive label (except sometimes within a range, e.g. with SUM() or LOOKUP()), and there should be no used or referenced cells lying in odd places or outside the main area of the spreadsheet. You can easily spot any blank (empty) input cells, as the Dependency Map function highlights these in a different colour."]
[]
[null,"However, note that an incorrect reference in a formula could result in blank input cells in any part of a worksheet (e.g. row 1,048,576, or column XFD). If these invalid inputs were outside the used range of the worksheet (the range covering all non-empty cells), they would not be detected by the Dependency Map function. To find any such invalid inputs, you can use Map Risks (select option 6 under the Precedents tab) to check all cells which have blank cells as precedents."]
[]
[null,"By default the Dependency Map function shows just the five dependency types (including blank inputs) mentioned above. If you want to add a further dimension of error checking, you have the option also to show row or column differences. Provided that your options (in Preferences, under the Colours tab) are set to allow multiple colours in a cell, these will be shown as additional colours alongside the appropriate dependency type colour."]
[]
[null,"The Dependency Map function does not produce a report, as this would list nearly all cells in the spreadsheet and would be of little use. However, you can produce separate reports on each of the dependency types with the Map Risks function (options 1 to 5 under the Dependencies tab, or click the \"Inputs and other dependencies\" button)."]
[]
[null," You can also use the Map Risks function to generate a different kind of map showing constant numbers, calculations, and text cells (options 1 to 3 under the Other tab, or click the \"Constants, formulas and text cells\" button)."]
[]
[null,"Inter-Sheet Logic"]
[]
[null,"The Inter-Sheet Logic function uses the range of 64 group colours to highlight the cells in each sheet which have precedents or dependents (as appropriate) in other sheets. The cells are highlighted in specific colours indicating which other sheet they are linked to, and you can set your Preferences (under the Colours tab) to change the start colour. The first (start) colour is always used for links to external workbooks, rather than links to other sheets in the same workbook."]
[]
[null,"As well as highlighting links in colour, Rainbow can generate a report showing in tabular format how each sheet is linked to other sheets (and also showing the selected colour for each sheet). This report is in the form of an outlined worksheet; click the number 2 to the left of the column headers at the top to display the details of individual cells, and click the number 1 to collapse the details and show only the totals. Note that the external links shown in this report will ignore cells with a #REF! error, and so it may give a different result from the report under Report Objects."]
[]
[null,"The colour mapping can only show one link for each cell (the first inter-sheet link in the cell's formula for precedents), but the report will normally show all inter-sheet links for each cell, unless you select the option (under Quick Reporting) to report only the first link for each cell. This option can speed up report production, but is not necessary if you are producing a grouped precedents report (the default setting), as this particular report runs very quickly."]
[]
[null,"The default option to group copied cells together (where appropriate) in the report is normally preferable to the option to list each cell separately, particularly as the grouped precedents report runs much more quickly than the non-grouped version. The non-grouped report is useful if you need to see the links for specific individual cells, but for a large spreadsheet it may require a very large number of rows."]
[]
[null,"By default Rainbow will highlight in the report the logic flows (between two sheets) which have the highest number of links; the top 5% will be shown in red, and the next 10% in yellow. The purpose of this is simply to enable you to see at a glance any overall patterns in the inter-sheet logic, and (as with the Summary Report) you have the option in Preferences (under the Other Functions tab) to turn off the highlighting."]
[]
[null,"You also have an option in Preferences to expand the report to include within-sheet logic (as well as inter-sheet logic). The report will then include all cells with any precedents/ dependents, including precedents/ dependents in the same worksheet. This will give you a very complete picture of the logic flow, but it can also generate a rather large report. Note that within-sheet logic will be shown only in the report, and not in the colour mapping."]
[]
[null,"If you are running a Dependents report, Rainbow may occasionally find cells with so many dependents that it would take a very long time to generate the report. You can control how Rainbow handles this situation under the Criteria tab in Preferences."]
[]
[null,"If you were to select all sheets and run both a Precedents report and a Dependents report, you might expect that the two reports would show the same results but with the axes reversed. In the simplest cases this will indeed be true, but the results will be distorted whenever a calculation references a multi-cell range in another sheet (e.g. in a SUM() formula). This will count as one reference in the dependent sheet but as several in the precedent sheet. To complicate matters even further, under some circumstances each of the precedent cells may record multiple dependents (instead of just one)."]
[]
[null,"Map Risks"]
[]
[null,"The Map Risks function is one of Rainbow's most powerful features. It enables you to highlight cells and generate reports showing any or all of 28 different potential risks (detailed below). In practice you will find it is best not to run too many of the 28 analysis types at once, otherwise it can take too long and generate more information than you want. So when you first click Map Risks, you will find it has pre-selected 7 of the most popular options; these are the default set of \"favourites\", which you can clear and then re-select by clicking the Favourites button."]
[]
[null,"You can make a more precise selection of the risks you want to analyse either by selecting them individually (under the Precedents, Formulas, Dependencies or Other tab) or by clicking one of the five \"preset\" buttons (e.g. \"Inconsistent precedents\"), each of which selects a group of options. Later you may end up with a standard set of a few analysis types that you use regularly, and you can then click the Save favourites button to set these as your default choice instead of the original 7 options (which you can always restore if you click Restore Defaults, under Preferences). "]
[]
[null,"The last two preset buttons (\"Inputs and other dependencies\" and \"Constants, formulas and text cells\") are different from the first three. The first three buttons highlight individual cells which are potential risks, whereas the last two are intended to generate colour \"maps\" of the structure of a worksheet which can indicate poor spreadsheet design. Reporting on individual cells is not usually relevant for these last two preset buttons, and is turned off by default (though you can turn it on if you wish)."]
[]
[null,"You will notice that the \"Inputs and other dependencies\" button generates the same \"map\" as the Dependency Map function. Dependency Map provides a fast and direct way to generate this map, whereas the \"Inputs and dependencies\" button within Map Risks allows you to select individual dependency types and optionally  generate reports and insert cell comments."]
[]
[null,"On the right of the Map Risks form is the Insert Comments box. If you select this, Rainbow will insert a comment (cell note) on each highlighted cell listing all the analysis types found in this cell, even if there are more analysis types than can be shown with colour highlighting (depending on your Maximum colours setting under the Colours tab within Preferences). However, the Insert Comments option is best used with caution, as it can increase the size of a file by a factor of up to 10 times."]
[]
[null,"Note that with all the options on the Precedents tab, and also with Formatting differences (on the Formulas tab), Rainbow will use column F of the corresponding report to show you the details of the formatting or other issues that it has found (as explained further below). And where Rainbow has found a block of copied cells, it may not be able to show exact details of the issues for each cell in the block, though it will attempt to provide as much information as it usefully can."]
[]
[null,"Below is a brief description of each of the 28 options, in the order they are shown on the Map Risks form."]
[]
[null,"PRECEDENTS"]
[]
[null,"• 1)  Inconsistent precedent copies are cells which are copied from another cell above or to the left, but where the cell type of one or more of the cell's precedents is different from the cell type of the corresponding precedent of the original cell from which it is copied (which is shown in column G of the report). By default (depending on your Preferences) this option also shows precedent ranges which vary in size (combining absolute and relative references), but it ignores differences in number format. This may seem complicated, but it provides a level of deep analysis which can indicate problems such as where a precedent should have been (and has not been) specified as an absolute reference, or where a formula has been copied beyond the end of the relevant source values. Following are two example entries in column F of the report for this option:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C96] C55 Date → Formula, B61:C61 1-cell → 2-cells"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"If column G of the report shows \"B96\", this entry in column F indicates that cell C96 is copied from B96, and that (a) cell B96 refers to B55, which is a date, whereas in cell C96 the corresponding precedent C55 is a formula, and (b) cell B96 refers to $B61:B61 (1 cell), which in cell C96 has become $B61:C61 (2 cells)."]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[H39] Changed internal precedents"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"You may occasionally see this entry in the report, meaning that the formula in cell H39 has some complexity which has caused Excel to process its precedents in a different order from the cell from which it is copied. This does not mean that there is an error, but it suggests that the formula is sufficiently complex to deserve careful checking."]
[]
[null,"• 2)  Precedent block inconsistencies refers to cells with a block of precedents that includes more than one type of cell within the block. By default (depending on your Preferences settings) this option also shows where there are row or column differences within the block, but it ignores differences in number format. Precedent block inconsistencies are not necessarily a problem, but they may indicate that the precedent block has included too many cells, e.g. that a SUM() calculation has included a subtotal as well as the basic data. Following is an example report item (column F):"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C87] {C51:C86} C55 Value → Blank, C63 Column Difference"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This indicates that cell C87 refers to a block of precedents C51:C86, and that within this block (a) cell C55 is blank, whereas the preceding cell C54 is a numeric value, and (b) cell C63 contains a different formula from the preceding cell C62."]
[]
[null,"• 3)  Precedents only part of block is in some ways the reverse of the preceding option; it shows cells with a block of precedents which forms part of a larger block of cells of the same type. This can  help you spot one of the most classic spreadsheet errors: a SUM() calculation that has failed to include all the relevant basic data cells. In order to show the most relevant items, Rainbow only looks for additional rows or columns where the block already has more than one row or column and extends beyond the row or column of the dependent cell, and it highlights in yellow where the additional row or column is a direct copy from the current block. Here is an example report item:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C134] {C97:M99} ↑C96:M96, ↓C100:M100, ≡→N97:N99"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This item will be highlighted in yellow unless you have turned off highlighting (in Preferences, under Other Functions). It indicates that cell C134 refers to the precedent block C97:M99, and that the cells above (C96:M96) and below (C100:M100) this block are all of the same type as in the block itself. In addition, the cells to the right (N97:N99) of the block all contain formulas that are a direct copy (as shown by the ≡ symbol) of the formulas on the  right-hand edge (M97:M99) of the block."]
[]
[null,"• 4)  Precedents referring forwards means cells which have precedents (in the same sheet) either on a row underneath or in a later column on the same row. This is not a problem for a modern spreadsheet program like Microsoft Excel, but it can indicate where the flow of the calculation logic is particularly complex and may need additional checking. Below is an example report item:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[D53] →F53:AS53, ↓F125"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This shows that cell D53 refers to cells F53:AS53 in the same row to the right and that it also refers to cell F125 in a later row."]
[]
[null,"• 5)  Precedents of different cell type shows cells which have precedents of a different cell type from the cell itself, for instance numeric cells which have a text cell as a precedent. This is not necessarily incorrect (cp. =LEN(A1) with A1 as text), but sometimes it can indicate a potential problem, for instance where a SUM() calculation includes text cells. Note that if you are doing this analysis together with Precedent block inconsistencies (which similarly scans blocks of precedents), you can speed up processing by selecting (in Preferences, under the Map Risks tab) to check only the corners of blocks (e.g. A1, A4, D1, D4 in block A1:D4). An example report item might be:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C104] Formula ← B41:B43 Blank, C66 Logical, C8 Text"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This indicates that cell C104 is a numeric formula, and it has blank precedents in cells B41:B43, a logical (TRUE or FALSE) precedent in C66, and a text string precedent in C8."]
[]
[null,"• 6)  Cells with blank precedents is in some ways the inverse of Blank input cells under the Dependencies tab; it shows the dependents of blank input cells. However, it is more comprehensive than the Blank input cells option, because (unlike the latter) it will report on dependents of blank input cells even if the input cells are outside the used area of the worksheet (e.g. in row 1,048,576, or column XFD). Instances of blank input cells outside the used area are more likely to be errors, and so are highlighted in yellow.  An example of this is the following report item:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C71] V13:AN13; [E71] → AP13"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This item (which will be highlighted in yellow unless you have turned off highlighting, in Preferences under Other Functions) shows that cell C71 has a block of blank precedents in V13:AN13, and also that cell E71 (which is copied from C71) has a blank precedent in cell AP13 which is outside the used area of the worksheet (as indicated by the → symbol)."]
[]
[null,"• 7)  Cells with  error values are cells with a value of #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! or #VALUE!. Many of these cells display an error value because they have one or more precedents with error values, not because of any problem with the cell itself. So the report for this option distinguishes between dependent error cells (for which it shows the precedents with error values) and error source cells (which are highlighted in yellow). And if you prefer, you can choose (under the Map Risks tab within Preferences) to show only the error source cells and ignore the dependents. Following are examples of the two different types of report item for this option:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C17] #DIV/0! → Error source cell"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This item (which will be highlighted in yellow unless you have turned off highlighting or chosen to show only error source cells) indicates that cell C17 has the error value #DIV/0, and that this is due to a division by zero in the formula of the cell itself (not inherited from another cell)."]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[L49] #DIV/0! ← C17"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This shows that cell L49 also has the error value #DIV/0, but that this value is inherited from cell C17, which is one of the cell's precedents."]
[]
[null,"FORMULAS"]
[]
[null,"• 1)  Hard-coded numbers (all) refers to  cells where an input number has been combined with a formula. Formulas containing a hard-coded 0 or 1 are very common, and so will not be highlighted, but this option (unlike the one below) will highlight all other hard-coded numbers. By default, Rainbow will include as hard-coded numbers any cells containing a formula such as \"=365/4\", which may be regarded as bad practice but is not generally dangerous. However, you can change your Preferences (under the Map Risks tab) to highlight only cells which combine a hard-coded number with a reference to another cell, e.g. \"=C63/4\"."]
[]
[null,"• 2)  Multi-digit hard-coded numbers refers to formulas containing a hard-coded value of 10 or above, or a decimal value. These are likely to represent more significant items of hard-coded data, whereas single-digit numbers (highlighted by the preceding option) may well be nothing more than parameters for functions such as HLOOKUP() and VLOOKUP()."]
[]
[null,"• 3)  Row differences (differences in cells along a row) can be a very useful indicator of inconsistencies in the calculations, though you may find it more helpful to use Formula Groups to check the different formulas in the spreadsheet, especially as Row differences and Column differences only show changes in formulas; they do not show (for instance) formulas overwritten with hard-coded inputs. Note that Row differences and Column differences cannot be grouped like other Map Risks options, and so they are processed after the other 26 options (and the corresponding reports come after the reports for the other options)."]
[]
[null,"• 4)  Column differences (differences in cells down a column) are similar to row differences, and will mainly be relevant for spreadsheets where the calculations are structured by columns rather than by rows."]
[]
[null,"• 5)  Formatting differences shows cells which are of a different type or different number format from the cells around them, and it also finds cells where the formula has been set to be hidden. If the \"map\" generated by this option displays a lone cell with formatting differences in the middle of a homogenous block of cells, this may well indicate a potential problem or error. An example report item could be:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C119] Hidden Formula, «General» → «0.00%»"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This indicates that cell C119 has been set with its formula hidden, and also it has a percentage format rather than the General number format of surrounding cells."]
[]
[null,"• 6)  Complex formulas are cells with a formula which contains at least a specified number of characters or a specified number of levels of nested IF() statements. The defaults are 100 characters or 2 levels of nested IF() statements, but you can click Preferences and set your own values (under the Criteria tab)."]
[]
[null,"• 7)  Array formulas are a special type of formula that Microsoft Excel can use to calculate results across an array (block) of cells. They can be a very effective method of performing certain complex calculations, but they need careful checking to ensure they are generating the correct results."]
[]
[null,"DEPENDENCIES"]
[]
[null,"• 1)  Blank input cells are cells which are empty but which are used in one or more calculations. There may be no problem if they are part of a larger block of cells, but individual blank input cells may indicate that an input value is missing or a formula elsewhere has been mis-typed."]
[]
[null,"• 2)  Non-blank input cells are a normal part of the calculation logic, as also are the following three dependency types. They are not significant on their own, but together they can provide a useful \"map\" of the worksheet. Note that (depending on your settings under the Criteria tab in Preferences) Rainbow may skip processing dependents for some cells where it is taking too long to do so. Where this happens, Rainbow will report the details in the Rainbow Log file (click User Guide, Rainbow Log)."]
[]
[null,"• 3)  Intermediate workings are calculations which provide the inputs to one or more further calculations."]
[]
[null,"• 4)  Output calculations are calculations which have no further dependent calculations."]
[]
[null,"• 5)  Labels are cells which are not empty, but which are not used in any calculations. Usually they are text cells serving as headings (labels) for a row or a column."]
[]
[null,"• 6)  Lookup and related functions finds cells containing any of these functions: HLOOKUP(), INDEX(), INDIRECT(), LOOKUP(), MATCH(), OFFSET(), VLOOKUP(). All these functions have the effect of determining the precedents of a calculation on the basis of other parameters. Consequently, unless carefully checked, they can very easily give rise to incorrect results."]
[]
[null,"• 7)  Lookup requiring ordered list is much more specific than the preceding option. It finds instances of the three LOOKUP() functions where they may not return the correct result unless the list of looked-up values is sorted in ascending order. Clearly in these circumstances there is even greater potential for error."]
[]
[null,"OTHER"]
[]
[null,"• 1)  Constant numbers are cells which contain an input number (i.e. not a formula). This and the following two options can provide a useful \"map\" of a worksheet, similar to but less specific than the Dependency Map."]
[]
[null,"• 2)  Calculated numbers are cells containing a formula which calculates a number."]
[]
[null,"• 3)  Text values are cells containing text rather than a number."]
[]
[null,"• 4)  Numbers formatted as text are numbers which have been formatted (e.g. by preceding them with a quote mark ' ) so that Excel will interpret them as textual rather than numeric values. By default (depending on your settings under the Map Risks tab within Preferences), this option will also find numbers which are preceded or followed by up to three or four letters, e.g. \"¥1000\" or \"568 ml\". If you then wish to convert these cells into simple numeric values, you can use the Format Editor (option 1)."]
[]
[null,"• 5)  Same font and background colour refers to cells where the content of the cell has been made invisible by setting it to the same colour (e.g. white) as the background - a technique often used to hide incidental parameters in a spreadsheet."]
[]
[null,"• 6)  Unlocked cells (all) are any cells where the protection status of the cell has been changed from locked (the default) to unlocked. Once you have highlighted unlocked cells with Map Risks, you can use the Format Editor (option 3) to change the colour if you want them displayed in a different colour on your spreadsheet."]
[]
[null,"• 7)  Unlocked formula cells are unlocked cells containing a formula. It is usual to unlock input cells and leave formulas locked in order to protect them from accidental change, and so unlocked formula cells may indicate a problem with spreadsheet design."]
[]
[null,"Report Objects"]
[]
[null,"The four Report Objects reports each produce a worksheet report (formatted as a data table) listing all occurrences of a certain type of object in the current workbook. They normally analyse the whole workbook, though you can select specific sheets if preferred."]
[]
[null,"The links report helps you find the exact location in your workbook of any external links to other Excel files (to see these click Edit Links in the Connections group on the Data tab in Excel). It searches for references to the external Excel files in all worksheets and also in any defined names and any charts (including chart sheets as well as embedded charts). Any worksheet-specific names will be shown preceded by the name of the relevant worksheet."]
[]
[null,"The comments report lists the text of all comments (cell notes) in your workbook, together with the author's name and the cell address and value."]
[]
[null,"The report on pivot and data tables lists all data tables, query tables (linked to an external data source) and pivot tables, and where possible it provides information about the source of the data."]
[]
[null,"The report on buttons and graphics lists all graphical objects (other than comments), with some additional detail about form controls (e.g. buttons) and chart titles. It also shows which objects (such as buttons) call a macro; these will correspond with the \"Macro buttons\" column in the Summary Report."]
[]
[null,"Manage Names"]
[]
[null,"The Manage Names function supplements Excel's Name Manager by allowing you to:"]
[null,"•  Generate an interactive report of all defined names (even hidden names)."]
[null,"•  Use this report to hide/unhide, list references, convert or delete any name."]
[null,"•  Convert all names to cell references (and optionally delete them)."]
[null,"•  Delete all names which are no longer valid (i.e. they refer to #REF)."]
[]
[null,"The first two options are related, and the second option will be disabled (greyed out) until you have used the first option to generate a report. Once you have generated a report, and you have it displayed on screen, you can then click again on the Manage Names button and the second option will now be enabled (no longer greyed out). Note that if you have generated more than one report on defined names, you will need to have the most recent report displayed on screen."]
[]
[null,"When you generate a report, normally Rainbow will check and report whether each name is used (referenced) anywhere in the workbook. This can be very useful, e.g. if you wish to delete all unused names, but it can take a long time for Rainbow to do this check. To turn off this feature, select \"Ignore\" in the box under \"Usage\"."]
[]
[null,"In the report, any hidden names are shown with the name in (brackets). Any invalid names start with \"(#REF!)\" in the Refers to column, so that you can sort or filter them, and any which refer to an Excel 4.0 macro have \"(XLM)\". The Used column shows which names are used (referred to) in calculations or charts in the spreadsheet. In the Comment/ (Description) column, Rainbow can show (in brackets) the description generated by Cell Inspector if there is no comment, but you can set your Preferences (under the Other Functions tab) to turn this off and just show comments."]
[]
[null,"For each name in the report, you can select an action in the final column from the drop-down list (click on the drop-down arrow by each cell). You can choose to:"]
[null,"•  Hide the name, so that it will not be shown in Excel's Name Manager (or unhide the name if it is hidden)."]
[null,"•  Generate a report listing where the name is used, i.e. all cell formulas and chart formulas in which the name is referred to."]
[null,"•  Convert the name to its referenced value, i.e. substitute the value of the name in all formulas where it is used (see example below)."]
[null,"•  Delete the name."]
[]
[null,"Once you have selected the required actions, click again on the Manage Names button, and select the second option to Do actions requested in the report. So for example if you wanted to delete all unused names, you could click on the drop-down arrow by \"Used\" to filter the list of names and show only those that are not used, then copy and paste \"Delete\" in the final column beside all these names, then simply click Manage Names and select this option to do the requested actions."]
[]
[null,"The third Manage Names option (Convert names to cell references) searches for all references to defined names within formulas and charts in the workbook, and substitutes the \"Refers to\" value of the name. So for example if you have defined the name \"Tomorrow\" with the value \"=TODAY()+1\", and cell C3 contains the formula \"=Tomorrow\", Rainbow will replace this with \"=(TODAY()+1)\". The name \"Tomorrow\" will then be unused (after Rainbow has made similar changes to any other cells in the spreadsheet which reference this name)."]
[]
[null,"After converting names, by default Rainbow will also delete the names (since they are no longer referred to anywhere), along with any other names that are not referred to anywhere in the spreadsheet. However, you can set your Preferences (under the Other Functions tab) so that Rainbow will not delete any names after converting. In any case, Rainbow will not delete the \"Print_Area\" and \"Print_Titles\" names used by Excel when printing, and it will also not convert or delete invalid (#REF!) or macro (XLM) names. You can delete these names individually by using the Action column in the report, or you can delete all invalid names in bulk with the fourth Manage Names option (below)."]
[]
[null,"The fourth Manage Names option provides a quick and simple way to delete all names which are no longer valid (where the name refers to #REF!)."]
[]
[null,"Note that, unlike Excel's Name Manager, the Manage Names report only includes \"defined names\",  i.e. it does not include the names of data tables. These are listed under Report Objects (option 3)."]
[]
[null,"Sample Data"]
[]
[null,"Most of Rainbow's functions are designed to help you analyse the logic of a spreadsheet rather than the data it contains. The Sample Data function helps fill this gap by enabling you to to generate a sample of data from a worksheet (e.g. a trial balance download) for testing. Rainbow can format the sample data as a report (added to the current report workbook), and/or it can highlight the sample rows in colour on the source spreadsheet. Before you click the Sample Data button, you can select the area of the source worksheet containing the data to be sampled, and Rainbow will then select the sample rows only from this area. "]
[]
[null,"You can use either random or periodic sampling methods. If you select random sampling, the first box under Sample selection will contain the random number seed. If you set this to 0, the seed (and the sample selected) will change each time you run Sample Data. If you set the seed to a positive number, the sample will be the same (for any given seed value) every time."]
[]
[null,"If you select periodic sampling, the first box under Sample selection will contain the row sampling interval, i.e. if you enter 4, Rainbow will select every fourth row. If you then enter 0 in the box underneath (total samples needed), Rainbow will simply select every fourth row until it reaches the end of the source data. However, if you enter a sufficiently large value for total samples needed, Rainbow will loop back to the beginning of the source data and continue selecting every fourth row. It will check and warn you if this means that it will select the same row of data more than once."]
[]
[null,"You can enter values in both of the boxes under Sample selection either by clicking the up and down arrows or by direct input. The maximum value in either box is 999. If you choose periodic sampling and enter 0 for total samples needed, Rainbow will let you know how many samples this will generate (from the whole worksheet or selected range), and if this is more than 999 it will prompt you to enter a different value."]
[]
[null,"If you select a range for the source data that has a row of data labels at the top, you can click the Top row - Labels box, and Rainbow will copy the data labels into the Sample Data report. Otherwise it will simply label the data columns as Col A, Col B etc."]
[]
[null,"Formula Groups"]
[]
[null,"The Formula Groups function analyses row and column differences to identify groups of copied formulas, and it then uses the colours from your Colour Chart settings (for more information see Colour Chart below) to highlight each group. This lets you see at a glance where there are inconsistent formulas in a row (which may indicate an error), and it allows you to check just one cell in a group and know that all the other cells are the same."]
[]
[null,"Rainbow allows you to group formulas either along rows (horizontally) or down columns (vertically). In most cases the default grouping along rows will be preferable; this identifies formulas which belong to the same group on a row even if they are not in adjacent cells. So for example in a multi-year spreadsheet with sets of 12 monthly columns followed by an annual summary column, Rainbow will highlight all the monthly columns on each row in one colour and all the annual columns in another colour. However, with formulas which are copied down columns (across multiple rows), it will use the same colour only if the formulas are in adjacent rows (in a single block of cells)."]
[]
[null,"You will find the grouping down columns (vertically) very useful in two situations:"]
[null,"•  where a spreadsheet has repeating sets of identical calculations every so many rows, perhaps calculating profit for different products. In this case the grouping down columns will give the same colour to each corresponding row (e.g. Gross Sales or Direct Costs) in the different sets. You can then see at a glance whether the repeating calculation sets are in fact identical, or whether there are variations in the patterns of colours."]
[null,"•  where the calculations in a spreadsheet are organised by columns rather than by rows. For instance a spreadsheet might list a company's stock items, one per row, with the columns showing opening and closing stock, production, unit price and cost, gross sales and net profit. The grouping down columns would check the calculations for consistency down each column, even if some rows had been left blank."]
[]
[null,"As well as mapping formula groups with colours, Rainbow normally generates a report which lists all unique formulas by row or column, showing the first and last occurrence and the number of occurrences of each formula, with a column where you can indicate that the formula has been checked. Under \"Formula listings in report\", you can choose whether column F of the report simply shows the relevant formula, or whether it displays a description of the cell as generated by the Cell Inspector function."]
[]
[null,"Normally Rainbow first uses the colour labelled Group 01, then Group 02, Group 03 and so on, but you can set your Preferences (under Grouped cell colours on the Colours tab) to start with a different colour. Whichever colour you start with, Rainbow will use all the colours between Group 01 and Group 64 if necessary in order to highlight each separate formula group. And if it needs to use more than 64 colours, it will simply begin again with the first colour."]
[]
[null,"Rainbow can use the available colours in two different ways. By default the Preferences for Grouped cell colours (on the Colours tab) are set to Rotate through all colours, which means that for each new formula group Rainbow will simply use the next available colour, going back to the beginning after it reaches Group 64. If you uncheck this option, Rainbow will use the minimum number of colours needed to distinguish different formula groups (and use consistent colours for formulas in each row or column); with this option it is unlikely that you will need more than the available 64 colours."]
[]
[null,"If Rainbow does run out of colours on a particular row (or column), it will continue to use different colours to indicate different contiguous formulas, but each colour may now represent more than one different formula within the row. If this happens, Rainbow will alert you and let you know which is the first cell on each row where it has run out of colours. In this situation it is sometimes helpful to try vertical grouping instead of horizontal grouping, or vice versa. Of course the Formula Groups report will still correctly list all the separate groups of formulas."]
[]
[null,"Cell Inspector"]
[]
[null,"The Cell Inspector is an interactive function which displays the precedents and dependents of the currently selected cell, and also provides a best-guess description of the meaning of each cell. It will take you to each precedent or dependent (double-click the relevant line or click the appropriate button) so that you can click on the underlying spreadsheet and explore the logic and meaning of each cell. Then click the back-arrow button to return to where you were before. The Cell Inspector title bar shows how many steps you are from the original cell (#1) where you started."]
[]
[null,"For most cells, the description will be in two parts separated by a dash, e.g. \"Net Profit - Year 1\". This indicates that the row heading is \"Net Profit\" and the column heading is \"Year 1\". Sometimes there may be only one heading, e.g. \"Net Profit\", and sometimes a heading may have two parts, e.g. \"All Products Net Profit\". To find the headings, Cell Inspector uses a probabilistic scoring system which cannot be guaranteed to be always accurate, but has been found to be reliable across a range of financial models, and can provide a very fast indication of possible faulty logic."]
[]
[null,"As an example, suppose you run Cell Inspector on cell H74 in sheet Operations, and Cell Inspector shows this as Availability Fee - 30/09/2015, with the formula \"=Inputs!H101*(1-C26)\". If you then look at Cell Inspector's list of Precedents and see that Inputs!H101 is Availability Charge - 30/09/2014, you can immediately spot that there may be a problem with misaligned columns in different sheets referring to different periods."]
[]
[null,"If you come across a complex formula, perhaps with several nested \"IF\" statements, you can click Parse Formula to see a structured layout of the formula which will quickly identify which are the relevant components of each \"IF\" statement. To go back to the standard Cell Inspector layout, just click Show Dependents."]
[]
[null,"Rainbow tries to make sure that the currently selected cell is always visible by placing the Cell Inspector dialog box in the top right of the screen, and if necessary by scrolling the spreadsheet to bring the cell into view. You can also click the Minimise button so that you can see any parts of the worksheet which may still be hidden by the Cell Inspector dialog box. And at any time you can click outside the dialog box to move around the worksheet and workbook."]
[]
[null,"Cell Inspector won't take you to precedents or dependents in closed external workbooks or in hidden sheets, though it will attempt to indicate precedents in closed workbooks. Cell Inspector will ignore dependents in hidden sheets when these are the first that it finds, and if the first two dependents are both hidden it will assume the cell has no dependents. So you should always use Rainbow's (Un)Hide/Protect function first if you think there may be any links to hidden sheets."]
[]
[null,"Compare Worksheets"]
[]
[null,"The Compare Worksheets function can work in several different ways, depending partly on what you select in the Compare Worksheets dialog box, and partly on what range(s) you have selected before you click Compare Worksheets:"]
[null,"•  It can compare one sheet in a workbook with any or all of the other sheets in the same workbook."]
[null,"•  Provided you have more than one workbook open, it can compare corresponding sheets (i.e. sheets with the same name) in two different workbooks."]
[null,"•  If you select the appropriate range(s) before clicking Compare Worksheets (see below for more detail on this), it can compare a particular multi-cell range (rather than the whole worksheet) with corresponding ranges in other sheets or another workbook."]
[null,"•  If you first use the Ctrl key (with the mouse) to select multiple areas (e.g. tables) in a single worksheet, it can compare the first area in the worksheet with all the others."]
[]
[null,"As with other Rainbow functions, when you first click Compare Worksheets, Rainbow will try to guess what workbook(s) and worksheet(s) you wish to compare. If you find it has guessed wrongly, simply click the Selection… button to change the selection. This will open a dialog box with two selection areas, for the workbook and worksheet(s) you want to compare (on the left) and what you want to compare them with (on the right). If you have chosen to highlight (Colour or Map) differences, Rainbow will highlight them in the worksheet(s) you select in the right hand selection area."]
[]
[null,"The title bar at the top of this selection dialog box will confirm what type of comparison is currently selected:"]
[null,"•  If you select two different workbooks, the title bar will show \"Comparing Workbooks\", and you will be able to select any or all worksheets in the left-hand area only, for comparison with corresponding sheets in the right-hand workbook."]
[null,"•  If you select the same workbook on both sides, the title bar will show \"Comparing Worksheets\", and you will then be able to select only one worksheet in the left-hand area. A worksheet that you select in the left-hand area will be deselected in the right-hand area, and you will be able to select any or all of the other worksheets to compare with it."]
[null,"•  If you select the same workbook on both sides, and you select the same one sheet in the right-hand area as you have selected on the left, then if this sheet has multiple areas selected, the title bar will show \"Comparing Areas in Sheet\"."]
[]
[null,"Once you have selected the workbook(s) and worksheet(s) you want in the selection dialog box, click OK to return to the main Compare Worksheets dialog box, where you can select other relevant options:"]
[null,"•  Comparison types lets you choose whether to compare formulas (i.e. the contents of cells) or values (the input or calculated results), and also whether to compare all cells, or to ignore (if comparing formulas) cells containing an input value, or (if comparing values) cells containing a calculated formula."]
[null,"•  If you have selected to compare formulas, Formula grouping can group together in the differences report all differences which result from copying the cells in both worksheets (rather like the Formula Groups function). This means that the report is much smaller, and lets you see immediately how many real changes have been made."]
[null,"•  The Ranges to compare option only applies if you have selected a multi-cell range to compare; it then allows you to compare either the same range across all sheets or different ranges in different sheets (see below for more details)."]
[]
[null,"The Compare Worksheets dialog box always appears with the same options initially selected. If you find that you regularly use a different set of options, you can save these as the default (initial) selection by clicking Save Defaults."]
[]
[null,"If you choose (under Ranges to compare) to compare different ranges in different sheets, the way the comparison then operates is dependent on whether you are comparing workbooks or worksheets:"]
[null,"•  If you are comparing two workbooks, then for any sheets in the source workbook which have a multi-cell range selected, Rainbow will compare the selected range with the same range in the corresponding sheet in the other workbook, and for any sheets which have just one cell selected, it will compare the same range as in the first selected worksheet."]
[null,"•  If you are comparing worksheets, Rainbow will compare the selected range in the source worksheet with a range of the same dimensions (rows x columns) in all other selected worksheets; for worksheets which have a multi-cell range selected, it will start the comparison at the top left corner of the selected range, and for any sheets which have just one cell selected, it will compare the same range as in the source worksheet."]
[null,"•  However, if you are comparing multiple ranges (areas) within a single worksheet, you don't need to select the whole of each area. Rainbow will compare the first selected range with other ranges of the same dimensions, starting the other range(s) at the top left corner of each other range (whether single-cell or multi-cell) selected on the worksheet."]
[]
[null,"If you choose (under Comparison types) to compare formulas only (ignoring input values), the comparison will ignore changes in constants (simple numbers and text) and only report differences where one or both cells contain a formula. However, it is important to appreciate that there may be constants which are not inputs (simply labels or memo cells), and inputs or hard-coded numbers (e.g. =365/4 or =B7*1.175) which are formulas, not constants."]
[]
[null,"A common reason for differences between two versions of a workbook is that rows may have been inserted or deleted in the later version. Rainbow Analyst will normally attempt to adjust for this by detecting how many rows have been inserted or deleted and inserting rows temporarily to bring the two workbooks into line with each other. It leaves the inserted rows in place while doing the comparison, and then automatically removes them at the end to restore both workbooks to their original state. (Note however that automatic row insertion is disabled when comparing multiple areas in a single sheet, to avoid unexpected interactions.)"]
[]
[null,"This feature can often reduce the number of reported differences by an order of magnitude. However, Rainbow will not always guess correctly where to insert rows, and so you can set your Preferences (under the Other Functions tab) to disable this feature if you wish, and you can also choose an option not to remove the automatically inserted rows at the end of the comparison."]
[]
[null,"This last option may be useful it there are row insertions or deletions in several sheets which depend on each other, in which case you may need to run the comparison, leave the adjustments in place, and then run it again. Generally however, where there are insertions or deletions in one sheet (e.g. Inputs) on which other sheets depend, all you need do is make sure this is the source sheet when you run the comparison, in order to ensure that the adjustments are made in this sheet before the dependent sheets are compared."]
[]
[null,"Where Rainbow finds differences, it will report the formulas in each cell as they appear after any adjustments that it has already made. Clearly this can mean that the formulas may refer to different rows from those which they referenced before the adjustments were made. However, Rainbow always shows the original address (before adjustments) of each cell in the columns headed \"Cell 1\" and \"Cell 2\"."]
[]
[null,"Sometimes Rainbow will be unable to detect insertions or deletions, even though they may be obvious to you. In this case, you may wish to help Rainbow by setting up marker cells which will tell it where the corresponding rows are located. In order to do this, you need to know that what Rainbow is looking for is a good (> 80%) match for the block of 15 cells to the right of the first column where there is a difference. It will search forward by up to 30 rows in each workbook looking for a matching block which will indicate the corresponding row."]
[]
[null,"Conversely Rainbow may sometimes detect an insertion or deletion where in fact there is some other reason for a difference between cells. You can stop this from happening simply by entering an identical value (e.g. 1) in both workbooks in the first column on the row where Rainbow found a difference which it attributed to an insertion or deletion."]
[]
[null,"Find Colours"]
[]
[null,"The Find Colours function checks the colour(s) of the selected cell against Rainbow's defined cell colours (as displayed if you click the Colour Chart function). It lists each colour, with the corresponding description, and you can click the Edit... button for each colour to go to the Colour Chart colour editing function for that colour."]
[]
[null,"You can also search for further occurrences of the same colour(s) in the worksheet, either by blocks of similar cells or one cell at a time, and searching either forwards or backwards. If you select Loose Matching, Rainbow will search for any cells that have any of the colour(s) in the current cell, whereas with Tight Matching, Rainbow will search only for cells with exactly the same colour(s) as the current cell."]
[]
[null,"Like Excel's own Find function, the Find Colours dialog box will let you click outside it on the underlying Excel worksheet. This means that if (for instance) you wish to find inter-sheet links to a specific sheet within your workbook after running the Inter-Sheet Logic report, you can click (in the report) on the colour that Rainbow has used for the relevant sheet, and then click Find Colours to identify this colour. You can then click outside the Find Colours dialog box and switch to the workbook, and simply click Next Block or Next Cell to find any relevant links."]
[]
[null,"Colour Chart"]
[]
[null,"The Colour Chart function provides an interactive display which lets you view and change the colours that Rainbow uses for all the various dependencies and cell types, as well as the 64 colours used to highlight formula groups (and inter-sheet logic and difference groups). It also links to the Find Colours function, allowing you to find occurrences of any specific Rainbow colour on the current worksheet."]
[]
[null,"So if you want to remind yourself of the cell highlighting colours, for instance after you have run the Dependency Map or Map Risks function, you can either select a specific cell and then click Find Colours to see the meaning of the colours in that cell, or you can click the Colour Chart button to view a complete table of the various colours. You can hover the mouse over any colour to see its exact RGB (Red, Green, Blue) values, and if you want to change a colour, simply click on it in the table to bring up a colour editing dialog box from which you can make a new selection."]
[]
[null,"This colour editing dialog box has two main options: you can click Change… to select a new colour, or Find... to find the existing colour in the current worksheet. If you click Find..., this will display the Find Colours dialog box, which is explained in the preceding section. If you click Change..., this will open Excel's own colour picker dialog, where you can choose from a colour hexagon or specify an exact RGB or HSL colour value."]
[]
[null,"Rainbow will check and alert you if the new colour you have chosen is not one of the current set of 92 colours, as this can cause the Find Colours function to give an incorrect result. If you do choose a duplicate colour, Rainbow will display an alert each time that the Colour Chart is initialised or changed, reminding you to change the duplicate colours."]
[]
[null,"Once you have selected a new colour, the colour editing dialog box will show the new colour above the old colour (with the Red, Green and Blue values for each). You can then click Undo to revert to the old colour, or Save Defaults to save the new colour as the standard colour for this cell type. If you close the dialog box (or click either of the two Edit... buttons below to move to the previous or next colour) without clicking Save Defaults, Rainbow will use the new colour for the current session, but will revert to the old colour for future sessions."]
[]
[null,"If you look closely at the RGB (Red, Green and Blue) values of the new colour you selected as displayed in the colour editing dialog box, you may notice that they are slightly different from what you might have expected. For instance if you selected pure red (which would normally have RGB values of 255, 0, 0), the colour editing dialog box would show RGB values of 254, 0, 0 (displayed as 254 000 000). This is because Rainbow deliberately makes a minimal alteration to your colour selection, in order to ensure that the Find Colours function will not confuse Rainbow's own colours with standard colours which might already be used elsewhere in the worksheet. "]
[]
[null,"Format Editor"]
[]
[null,"The first Format Editor option, to Convert numbers formatted as text, uses the same definition of \"numbers formatted as text\" as the corresponding option in the Map Risks function (on the Other tab), but instead of highlighting any relevant cells it replaces the textual values with the corresponding numbers. Like the Map Risks option, it can also (if selected in Preferences, under the Map Risks tab) deal with numbers preceded or followed by up to three or four letters, e.g. \"¥1000\" or \"568 ml\"."]
[]
[null,"The second option, to Clear cell colours and patterns, is a function that you  may find helpful before you start to use Rainbow's analysis features to apply colours (or patterns) to specified cells, or you can use it later to remove highlighting colours from a specific sheet or range. If you want to clear all cell colours in the workbook, you may prefer to use the Quick Clean-Up function."]
[]
[null,"The remaining three options in the Format Editor dialog box work differently from other Rainbow functions; they allow you to apply certain formatting operations to all cells with the same colour as the cell which is active when you click the Format Editor button. Consequently you need to make sure you have selected an appropriate cell (i.e. one of the type that you wish to change) before you click Format Editor. You can then change the cell colour, font colour or locked status of all cells that match the one you have selected."]
[]
[null,"Note that the final Format Editor option, to turn cell locking on or off, \"toggles\" the locked status of the active cell; if it is currently locked, this option unlocks it (and other matching cells), and if it is currently unlocked, this option locks it. So if the active cell is locked and you wish to turn on cell locking for other cells that match it, you will first need to unlock the active cell. You can do this with Excel's Format Cells dialog box: press Ctrl+1, or click the Home tab, then Format (in the Cells group), then Format Cells, then select the Protection tab."]
[]
[null,"You can use the last three Format Editor options in conjunction with other Rainbow Analyst functions. For instance you can click Map Risks, and then select the Dependencies tab and click Non-blank input cells to colour all (non-blank) input cells yellow. You can then select an input cell and click Format Editor to set all matching yellow cells as unlocked. You can now use (Un)Hide/Protect to protect the worksheet, and your input area is ready for use!"]
[]
[null,"When you are changing the formatting of cells in this way, Rainbow offers the options of Loose Matching or Tight Matching:"]
[null,"•  For new font colours, if you select Loose Matching, Rainbow will change any cells that have the same font colour as the current cell, whereas with Tight Matching, it will change only cells with the same font colour and exactly the same cell colour(s)."]
[null,"•  For new cell colours and for cell locking, if you select Loose Matching, Rainbow will change any cells that have any of the colour(s) in the current cell, whereas with Tight Matching, it will change only cells with exactly the same colour(s) as the current cell."]
[]
[null,"The Export and Import Colour Map functions on the split menu (drop-down arrow to the right of Format Editor) let you export and re-import a one-worksheet workbook with the current cell highlighting colours. This means that you can save a particular set of cell highlighting options, and then go back to them at any time later. All you need do is click Export Map Sheet, and then save the Colour Chart workbook (the filename and folder can be anything you choose). To go back to the saved highlighting options, simply open the saved workbook and then click Import Map Sheet."]
[]
[null,"You can also manually edit the colours or colour numbers in the Colour Chart workbook as a way of changing Rainbow's set of cell highlighting colours. However, unless you are making changes to a large number of colours, it is generally preferable to use the colour editing capability within the Colour Chart function."]
[]
[null,"(Un)Hide/Protect"]
[]
[null,"The Hide selected sheets option in the (Un)Hide/Protect dialog box provides a quick way to hide several sheets, including chart sheets as well as worksheets. With the default Very Hidden option (in the (Un)Hide box under the Selection... button), the hidden sheets will be effectively invisible in normal use.  If you choose the Hidden option, the hidden sheets can be unhidden by right-clicking any sheet tab, or from the Hide & Unhide item on the Format menu in the Cells group on the Home tab in Excel."]
[]
[null,"The Unhide all sheets option can unhide any hidden chart sheets as well as worksheets. With the default Very Hidden option, it will unhide both ordinary hidden sheets and any sheets which have been set as very hidden by Rainbow or by another Excel macro.  If you choose the Hidden option it will only unhide sheets which have been hidden in the normal way (i.e. those listed as hidden when you right-click any sheet tab or under the Hide & Unhide menu item mentioned above)."]
[]
[null,"The Unhide rows and columns option will unhide any hidden rows and columns in the selected sheets, and it will display a list of all rows and columns that it has unhidden."]
[]
[null,"The Protect / Unprotect selected sheets options offer a quick way to protect or unprotect several sheets at once, either with or without a password. Rainbow will also protect or unprotect the workbook structure at the same time."]
[]
[null,"Preferences"]
[]
[null,"This function displays the Preferences dialog box, so that you can customise Rainbow to reflect your own preferences. When you have set your preferences, you can click Save Defaults to save your preferences to disk as your new defaults. Alternatively you can click Apply to use your new preferences only until you close Rainbow or exit from Microsoft Excel."]
[]
[null,"The Preferences dialog box has five tabs: Reports, Colours, Criteria, Map Risks, and Other Functions. Under the Reports tab, the first set of options tells Rainbow what to do when no workbook has been selected for it to use for reports. This means that, for instance when you first run a report after starting Microsoft Excel, Rainbow can go ahead and generate the report without first having to stop and ask you which workbook to put it in. You can tell Rainbow to do one of four things:"]
[null,"•  First look for a workbook that already appears to contain one or more Rainbow reports (based on cell A2 in the last sheet), and if it does not find one, then to create a new workbook for the report. This is the default option."]
[null,"•  Put the report in the current workbook, i.e. the workbook that is currently visible on your screen. This could be the workbook you are reporting on, or it could be a general Rainbow Reports workbook, and you could then use the Selection... button on the Rainbow dialogue box to select a different workbook to report on."]
[null,"•  Put the report in a new workbook, even if there might be a workbook already open which contains Rainbow reports."]
[null,"•  Ask the user to select a current or new workbook. This was the only option in earlier versions of Rainbow."]
[]
[null,"The remaining options under the Reports tab (in the Individual Reports box) let you select to include all reports in the same workbook (the default setting) or to generate each report as a separate workbook. You can also tell Rainbow to routinely select all worksheets for analysis rather than just the current sheet(s). And you can choose whether Excel displays a warning when you click on a hyperlink."]
[]
[null,"Under the Colours tab, the most important set of options concerns the use of multi-colour cells. This feature was introduced in Rainbow 7.0, replacing the use of patterns in previous versions, but you can choose to turn it off if you wish Rainbow to behave more like previous versions and show only one colour in each cell. If you allow multiple colours, Rainbow will then be able to show multiple characteristics in a single cell. For instance, the Map Risks function might highlight a cell with three colours, showing that it contains (1) a calculation which has (2) a hard-coded number, and it is also (3) unlocked."]
[]
[null,"Under Multi-Colour Cells on the Colours tab, if the option to allow multiple colours in a cell is enabled, you can then choose various further options:"]
[null,"•  You can set the maximum number of colours allowed in a cell. This can be up to 16 colours, but a high number will slow down Rainbow's analysis, so a setting of 4 to 6 is recommended."]
[null,"•  You can choose what Rainbow does if a cell already contains the maximum number of colours and it wishes to add a further colour. Since Rainbow adds colours from the right, it can either  replace the last colour (on the right) with the new colour and leave the other colours in place, or (the default option) remove the first colour on the left of the cell, shift the other colours along, and then add the new colour on the right."]
[null,"•  You can choose the notional angle (from 1 to 180 degrees) of the boundaries between the colours. A purely vertical or horizontal angle can be confused with the boundaries between neighbouring cells, and so a diagonal angle of around 20 degrees is recommended. The precise actual angle will be different from the notional angle, depending on the width of the cell."]
[]
[null,"The other options under the Colours tab (in the Grouped cell colours box) let you choose the Start colour number, i.e. which of the 64 group colours Rainbow should start from when selecting colours for the Inter-Sheet Logic, Formula Groups and Compare Worksheets functions. And the option to Rotate through all colours tells Rainbow to work through all the 64 colours in turn, rather than trying to minimise the number of colours it uses."]
[]
[null,"The four sets of options under the Criteria tab allow you to set the threshold values for certain Rainbow functions:"]
[null,"•  Under Excess same-sheet dependents, you can choose what Rainbow does (in Inter-Sheet Logic, Dependency Map, and the Dependencies tab in Map Risks) when it finds a cell with so many dependents in the same sheet that it takes Excel a long time to list them. If you enter 0 (zero) here, Rainbow will keep going regardless of how long it takes, whereas if you enter a positive number of seconds (the default is 5), then if it takes longer than this to list the dependents for any one cell, Rainbow will skip processing dependents for all further cells on the same row. Where this happens, Rainbow will report the details in the Rainbow Log file (click User Guide, Rainbow Log)."]
[null,"•  Under Complex Formulas, you can set the criteria for marking a formula as complex under the Formulas tab in Map Risks. The number of characters is also used by the Summary Report to decide when to highlight the average and maximum length of formulas."]
[null,"•  The value under Comments (Summary Report) determines the number of comments that form the threshold for Rainbow to highlight this item in the Summary Report."]
[null,"•  The entries under Large sheets (Summary Report) determine the number of rows and columns that form the criteria for Rainbow to highlight a sheet as large in the \"Total rows x cols\" column of the Summary Report."]
[]
[null,"The six sets of options under the Map Risks tab all relate to options within the Map Risks function:"]
[null,"•  Under  Inconsistent precedent copies, you can select whether you want Rainbow to include cases where a block changes size as the formula is copied along a row; for instance =SUM($A1:B1) includes 2 cells, but it will change to =SUM($A1:C1) (including 3 cells) and so on as the formula is copied along a row. You can also select whether to include inconsistencies in the number format of cells (and this selection also applies to the analysis of Precedent block inconsistencies)."]
[null,"•  Under Precedent block inconsistencies, you can select whether you want Rainbow to check for row differences and column differences (as well as inconsistent cell types and formats); often (e.g. in a SUM() calculation) row and column differences can be a good indicator of possible errors."]
[null,"•  Under Precedents of different cell type, you can choose whether Rainbow should speed up processing by checking only the corner cells in a block of precedents (e.g. A1, D1, A4, D4 where the formula is =SUM(A1:D4)), or whether it should check all cells in the block."]
[null,"•  Under Error values, you can select whether you want Rainbow to highlight and report on all error cells (including those where the error is simply a result of errors in one or more of the precedents), or whether it should show only the error source cells (from which the error originates)."]
[null,"•  Under Hard-Coded Numbers, you can choose whether Rainbow should include \"numbers-only\" formulas such as \"=365/4\" as an instance of hard-coded numbers (under the Formulas tab in Map Risks), or whether it should only look for formulas which include references to other cells."]
[null,"•  Under Numbers formatted as text, you can indicate whether you want Rainbow to recognise numbers preceded or followed by up to three or four letters, e.g. \"¥1000\" or \"568 ml\", as well as straightforward cases of numbers formatted as text. Note that this option affects both the Map Risks function (under the Other tab) and the Format Editor function (option 1)."]
[]
[null,"The four sets of options under the Other Functions tab relate to a number of other Rainbow functions:"]
[null,"•  Under Summary Report, you can indicate whether you want Rainbow to highlight (in yellow and red) specific results which may indicate potential problems. You can also select whether it should insert comments (cell notes) to help you find more detailed analysis of specific items in other Rainbow functions. The option to control highlighting also applies to the highlighting in some Map Risks reports."]
[null,"•  Under Inter-Sheet Logic, you can similarly tell Rainbow whether or not to highlight the logic flows with the highest frequencies. You can also choose to include within-sheet logic (i.e. formulas referencing cells on the same sheet) in the Inter-Sheet Logic report."]
[null,"•  Under Manage Names, you can select whether Rainbow uses the descriptions generated by Cell Inspector in the Comments/ (Descriptions) column of the report, and whether it should delete names after it has converted them to cell references."]
[null,"•  Under Compare Worksheets, you can control whether Rainbow automatically inserts rows where it considers they have been deleted, and then whether it deletes them after insertion."]
[]
[null,"The Restore Defaults function on the split menu (drop-down arrow to the right of Preferences) lets you restore Rainbow back to its original state (as initially downloaded). This function removes any licence data, and so you will need to click User Guide and About Rainbow to re-enter your registration code."]
[]
[null,"User Guide"]
[]
[null,"The User Guide function simply displays this User Guide as an Excel spreadsheet. You can then print, save or close it just like any normal spreadsheet."]
[]
[null,"Rainbow Log"]
[]
[null,"This function displays the contents of the Rainbow log file (RainbowLog.txt), which is stored in a folder for temporary files, usually at C:\\Users\\[Your user name]\\AppData\\Local\\Temp\\RainbowAnalyst. If you have an individual Rainbow Analyst licence, you can check the location of this folder on your computer if you click User Guide and About Rainbow."]
[]
[null,"The log file holds details of sheets, rows and columns recently unhidden or unprotected (with the Quick Clean-Up or (Un)Hide/Protect functions), defined names converted or deleted, potential risks identified with Map Risks, text cells converted to numbers, rows where dependent checking has been skipped, and duplicate highlighting colours, and it also has information about any system errors (including occasions when Rainbow processing was interrupted)."]
[]
[null,"To ensure that the file does not become too large, it only holds information for the most recent day on which any of these events occurred, but you can make a copy of it if you need to preserve any of these details."]
[]
[null,"You can click outside the Rainbow Log dialog box on the underlying Excel worksheet. This means that you can keep the contents of the log file visible on screen while you navigate to find the sheets or cells that it refers to."]
[]
[null,"About Rainbow"]
[]
[null,"The final menu item displays copyright and licensing information, and allows you to enter the registration code if this is currently an unlicensed copy. If you have a single licence, the licence requires that you agree to use the registration code only to license one copy of Rainbow Analyst."]
[]
[null,"If you have registered, then the About Rainbow box also displays information about how to access support, and where to find the Rainbow log file."]
[]
[null,"Installation and licensing"]
[]
[null,"Rainbow Analyst is installed simply by copying the Rainbow add-in (.xlam) file to your Excel Add-Ins folder. You can find this from within Excel by clicking the File tab (or Office Button), then Excel Options, Add-Ins, and Manage Excel Add-ins (click the Go button), then clicking the Browse button. This will open a browse window in the Add-Ins folder, from which you can see where the folder is located. Recent versions of Rainbow have also included an installation (.exe) file which automatically copies the Rainbow add-in file to the correct folder."]
[]
[null,"After you have copied the Rainbow file to the Add-Ins folder, you can load it by going to the Add-Ins window (as above) and then clicking the check box labelled Rainbow Analyst. You can remove Rainbow at any time simply by deleting the Rainbow add-in file from your Add-Ins folder."]
[]
[null,"Rainbow Analyst will initially install itself as a 30-day free trial copy. This may be converted to a licensed copy by obtaining a registration code from the author. The code is linked to your Microsoft Office user name, which you can see if you click the Office Button and Excel Options. The licence requires you to use the registration code only for one copy of Rainbow Analyst."]
[]
[null,"Copies of Rainbow Analyst can be given to other users (with different Office user names), and these will automatically become 30-day free trial copies. A licensed copy will also convert itself into a 30-day free trial copy if the registered user changes to a different Office user name. If this happens, you will need to restore the previous Office user name or obtain a new registration code from the author."]
[]
[null,"Mike Shallcross has asserted the moral right to be regarded as the author of Rainbow Analyst, and he holds the copyright in the program code. He can be contacted by email at mike@themodelanswer.com"]
[]
[null,"Note that although Mike has exercised due care and diligence in developing and testing this software, he cannot guarantee its performance, and he cannot be held responsible for any loss or damage, however caused, in connection with the use of this software."]

{"sheet":"Colours","index":1,"n_rows":97,"n_cols":9,"rows_kept":97,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Rainbow Analyst Cell Colour Sheet"]
[]
[null,"Cell type","Current","Red","Green","Blue","Reset","TestAll","Favourites"]
[]
[null,"Inconsistent precedent copies",null,0,204,254,null,true,false]
[null,"Precedent block inconsistencies",null,203,51,153,null,true,false]
[null,"Precedents only part of block",null,153,0,203,null,true,true]
[null,"Precedents referring forwards",null,203,203,254,null,true,false]
[null,"Precedents of different cell type",null,51,51,152,null,true,false]
[null,"Cells with blank precedents",null,0,152,102,null,true,true]
[null,"Error values",null,102,203,203,null,true,true]
[null,"Hard-coded numbers (all)",null,102,0,203,null,false,false]
[null,"Multi-digit hard-coded numbers",null,153,102,254,null,false,true]
[null,"Row differences",null,51,254,254,null,true,false]
[null,"Column differences",null,0,153,203,null,true,false]
[null,"Formatting differences",null,254,51,153,null,true,false]
[null,"Complex formulas",null,153,203,0,null,false,true]
[null,"Array formulas",null,51,204,254,null,false,false]
[null,"Blank input cells",null,254,0,0,null,true,false]
[null,"Non-blank input cells",null,254,254,0,null,true,false]
[null,"Intermediate workings",null,0,254,0,null,true,false]
[null,"Output calculations",null,0,0,254,null,true,false]
[null,"Labels",null,254,0,254,null,true,false]
[null,"Lookup and related functions",null,153,203,153,null,false,false]
[null,"Lookup requiring ordered list",null,203,102,51,null,false,false]
[null,"Constant numbers",null,152,102,152,null,false,false]
[null,"Calculated numbers",null,0,203,102,null,false,false]
[null,"Text values",null,102,153,203,null,false,false]
[null,"Numbers formatted as text",null,203,102,153,null,false,true]
[null,"Same font and background colour",null,102,254,51,null,true,true]
[null,"Unlocked cells (all)",null,254,204,51,null,true,false]
[null,"Unlocked formula cells",null,152,102,102,null,true,false]
[null,"Group 01",null,0,254,51]
[null,"Group 02",null,254,0,51]
[null,"Group 03",null,51,51,254]
[null,"Group 04",null,152,0,152]
[null,"Group 05",null,203,153,153]
[null,"Group 06",null,153,51,203]
[null,"Group 07",null,204,254,0]
[null,"Group 08",null,51,153,254]
[null,"Group 09",null,102,152,102]
[null,"Group 10",null,153,254,51]
[null,"Group 11",null,203,51,0]
[null,"Group 12",null,153,204,254]
[null,"Group 13",null,203,102,0]
[null,"Group 14",null,152,152,152]
[null,"Group 15",null,254,254,102]
[null,"Group 16",null,102,102,152]
[null,"Group 17",null,203,203,51]
[null,"Group 18",null,203,102,203]
[null,"Group 19",null,254,51,51]
[null,"Group 20",null,102,102,254]
[null,"Group 21",null,101,101,101]
[null,"Group 22",null,254,102,51]
[null,"Group 23",null,204,254,51]
[null,"Group 24",null,254,153,254]
[null,"Group 25",null,102,0,254]
[null,"Group 26",null,204,254,102]
[null,"Group 27",null,203,0,203]
[null,"Group 28",null,152,0,102]
[null,"Group 29",null,102,203,153]
[null,"Group 30",null,0,51,203]
[null,"Group 31",null,0,203,153]
[null,"Group 32",null,254,204,153]
[null,"Group 33",null,254,102,254]
[null,"Group 34",null,102,203,51]
[null,"Group 35",null,204,51,254]
[null,"Group 36",null,153,254,204]
[null,"Group 37",null,254,102,204]
[null,"Group 38",null,0,102,203]
[null,"Group 39",null,153,254,0]
[null,"Group 40",null,203,102,102]
[null,"Group 41",null,0,203,203]
[null,"Group 42",null,204,204,254]
[null,"Group 43",null,254,51,0]
[null,"Group 44",null,152,51,152]
[null,"Group 45",null,51,102,152]
[null,"Group 46",null,153,203,102]
[null,"Group 47",null,153,203,203]
[null,"Group 48",null,153,0,254]
[null,"Group 49",null,102,152,51]
[null,"Group 50",null,51,254,102]
[null,"Group 51",null,204,0,254]
[null,"Group 52",null,102,51,152]
[null,"Group 53",null,51,203,51]
[null,"Group 54",null,102,0,152]
[null,"Group 55",null,102,51,254]
[null,"Group 56",null,254,153,51]
[null,"Group 57",null,203,51,51]
[null,"Group 58",null,0,152,152]
[null,"Group 59",null,153,254,102]
[null,"Group 60",null,102,254,204]
[null,"Group 61",null,51,102,254]
[null,"Group 62",null,254,0,204]
[null,"Group 63",null,152,102,0]
[null,"Group 64",null,203,203,203]

{"sheet":"Preferences","index":2,"n_rows":112,"n_cols":7,"rows_kept":112,"cut":false,"hidden":false,"merged":[]}
["Preferences sheet"]
[null,null,null,null,null,"Current","Report"]
["Note mapping information in columns F:G"]
[]
["Current book/sheets and report book/sheets"]
[]
["User details"]
[]
["User code"]
["Expiry","00:00:00"]
[]
["Reporting"]
[]
["New workbook option",1]
["Reports in same book",true]
["Report book name"]
["Select all sheets",false]
["Disable hyperlink warnings",true]
[]
["Multi-Colour Cells"]
[]
["Allow overlap",true]
["Maximum colours",6]
["Shift left (FIFO)",true]
["Colour angle",20]
[]
["Mapping"]
[]
["Start colour",1]
["Rotate colours",true]
["Generate map",false]
[]
["Summary Report"]
[]
["Use highlighting",true]
["Explanatory comments",true]
["Rows threshold",1000]
["Columns threshold",100]
["Comments threshold",100]
[]
["Inter-Sheet"]
[]
["Generate report",true]
["Use highlighting",true]
["Include locals",false]
[]
["Risks and Dependencies"]
[]
["Generate report",true]
["Insert comments",false]
["Secs to show dependents",5]
[]
["Inconsistent copies"]
[]
["Changes in range size",true]
["Formatting differences",false]
[]
["Inconsistent precedents"]
[]
["Row/column diffces",true]
[]
["Different cell type"]
[]
["Only check corners",false]
[]
["Cell errors"]
[]
["Only originating cells",false]
[]
["Hard-coded numbers"]
[]
["Include all-numbers",true]
[]
["Complex formulas"]
[]
["Long formula chars",100]
["Nested IFs",2]
[]
["Numbers formatted as text"]
[]
["Detect leadg/trailg letters",true]
[]
["Defined Names"]
[]
["Use Cell Inspector",true]
["Delete names",true]
[]
["Formula Groups"]
[]
["Generate report",true]
["Use descriptions",false]
[]
["Comparison"]
[]
["Insert rows",true]
["Delete rows",true]
[]
["Comparison Form","Current","Defaults"]
[]
["Compare formulas",true,true]
["Compare values",false,false]
["Include all cells",true,true]
[]
["Grouped formulas",true,true]
["Separate formulas",false,false]
[]
["Entire sheets",true,true]
["Fixed range",false,false]
["Variable range",false,false]
[]
["Colour differences",true,true]
["Map differences",false,false]

{"sheet":"Control","index":3,"n_rows":999,"n_cols":27,"rows_kept":999,"cut":false,"hidden":false,"merged":[]}
["Control sheet"]
[null,null,null,null,"Book","Sheet","Cell",null,null,"Precede","Book","Sheet","Cell","Type","Value","Description","Visible",null,null,"Depend","Book","Sheet","Cell","Type","Value","Description","Visible"]
["WkBook"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]

{"sheet":"Reports","index":4,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Temp","index":5,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}