Formula Mapper Dialog Box
Create
Spreadsheet Maps Frame
l
For
selected worksheets
Maps will be created
for only the selected sheets in the active workbook.
l
For all worksheets
Maps will be created for all
worksheet in the active workbook including hidden and very
hidden worksheets.
Spreadsheet
Map Location Frame
l
In New
Workbook
This creates the formula maps
in a new workbook.
.
l
Overlay
In Mapped Workbook
This creates the formula maps
by overlaying cell formats on top of the original worksheet - this is slightly
harder to interpret but has the significant advantage of not having to jump
between the original worksheet and the map worksheet to see what is going on.
l
Identify
true unique formulae with numbers
Most formulae mappers only identify unique formulas. This mapper goes a step further and will number each unique
formula, for example, if a formula is repeated later in a worksheet it is
identified not only as a unique formula within its range but also the same as
the formula identified earlier. This numbering of ‘true’ unique formulas is of
great benefit when blocks of code are repeated in the same workbook/worksheet
(for example monthly time lines with a blank column inserted between years or
depreciation computations for each fixed asset category).
l
Do Not
Reset True Unique Formula Count Between Sheets
When the formula mapper is checking to see if a unique formula has been
repeated elsewhere in the workbook this flag affects whether identical formulas
in other worksheets are used in this search process.
l
Place
Unique Formulae In Comments
When an
unique formula is located a copy of it is placed in the associated cell
comment.
l
Show
Hidden Ranges As Grouped Ranges On The Map
When a worksheet is mapped, all
the parts of the worksheet are mapped including hidden rows and columns. When
this option is selected the hidden rows and columns in the worksheet are signified
on the worksheet map by using the grouping feature in Excel. In addition if any
rows or columns are grouped and groups are closed they will also be copied
across on to the worksheet map.
l
Highlight
Formulae That Evaluate To An Error
If a cell containing a formula
evaluates to an error it will be identified on the map by adding “.err” to the
formula mapping symbol for that cell reference e.g. “<” will become
“<.err”.
l
Before
Mapping Reset All Cells To Default Colours
Before mapping takes place the
cell background colour and cell font colour of each worksheet is set to the
default colour and all condition formats are removed from the worksheet. This
can facilitate the interpretation of overlay maps since all the colours on the
worksheet will relate to the formula map. Please note that conditional formats
override manually set cell formats, so if this option is not selected and there
is conditional formats on the worksheet the formula map may not display
correctly.
Spreadsheet
Map Options Frame
l
Map
Formulae
Cells containing formulae will
be mapped.
l
Show
Merged Formula Cell Regions With Red Border
Cells which have been formatted
as merged cells will be shown with a red border.
l
Map
Constants
Cells containing constants will
be mapped.
l
Copy
Values
Cells containing constants will
have their values copied across on to the map.
l
Range name locations are
identified when this option is selected.
l
Show
Name Of Range Name On Left Top Corner Of Border
Range name locations are
labelled with their associated names.
l
Array formulae are identified
with borders when this option is selected.
l
Put Map
Details In Map Worksheet Header
Places the filename, worksheet
name, time and date stamp and user name in the
worksheet header.
l
Provide
Map Legend
Adds a
worksheet which provides an outline explanation of the legend used on the
formula maps.