Range Finders

A Range Finder is used to find a cell or a range of cells in a spreadsheet. Range Finders are used in steps, where they define how to find the cell(s) to which the step should be applied. The list of Range Finders of the current step is located in the "Finders" tab in the Step View. Steps that work on HTML or XML pages use Tag Finders rather than Range Finders.

Overview

You can select between different starting points when you configure a range finder:

Find Specified Range
Here you specify (in Range) a cell or range of cells using almost ordinary Excel syntax. Keep in mind that (in contrast to Excel) the sheet name must be given.

The range can be specified in several ways using a Value Selector.

Find at Named Range
Here you specify in (Range) a previously defined named range as the starting point. It may have been defined by for example a Set Named Range step or a Loop in Excel step.

Once a range has been selected as the starting point it may be adjusted in several ways as specified by the Use property, which can make it both smaller or larger. See below for details.

Finally, the Use Upper Left Cell in Merged Cells property determines how to handle merged cells in the spreadsheet. Remember that in Excel, adjacent cells can be "merged" visually to form a larger cell with a single value. Excel considers the larger "merged cell" to have the same cell address as the uppermost and leftmost sub-cell, and the value of the "merged cell" is found at this cell address (only). This is mimicked accurately by Kapow Katalyst but it is not always convenient when doing automated extraction, especially as part of an iteration. Thus if you enable Use Upper Left Cell in Merged Cells and the range refers to a single sub-cell within a "merged cell", then it is modified to refer to the uppermost and leftmost sub-cell of the "merged cell" to make it easier to get at the contents.

The Use Property

The Use property of a Range Finder can specify the following kinds of adjustment to the range that is given as the starting point. Note that even though this may change the range considerably, it will always stay on the same sheet within the spreadsheet document.

Whole of Range
Use the range with no adjustment.
Cell At Position
Use a single cell specified by its column and row position relative to the given range. This may be used to select one of the cells within the range or to select a nearby cell (by using a column or row index that is negative or larger than the size of the range). See below for how to enter the position.
Cell That Matches
Use only one of the cells within the range, found by pattern matching. The matching may be done against the same three representations of cell values that you can choose in the Extract Cell step. By default the first match will be used, but you can select to use any other match, for example the second one, the last one, the next-to-last one etc. Note that the pattern must match the entire contents of the cell.
Column At Position
Use a column (a range that is one cell wide) specified by its position (relative to the given range) and its height. See below for how to enter the position and height of the column. This is a useful special case of Specified Part.
Row At Position
Use a row (a range that is one cell high) specified by its position (relative to the given range) and its width. See below for how to enter the position and width of the row. This is a useful special case of Specified Part.
Specified Part
Use a part of the page specified by its position relative to the given range, and by its width and height. See below for how to enter the position, width and height.
Whole Sheet
Use all of the sheet that contains the range.

How to Enter a Position

Some of the options for the Use property require stating a column and/or row position. Column and row positions are given in much the same way, but we will describe them separately.

Column positions are specified relative to the given range in one of the following ways:

By Index
Identifies a column by its offset relative to left side of the given range. 0 means the first column of the range, 1 means the second column etc. Negative numbers are permitted; for example, -1 means the column just to the left of the given range. It is also permitted to go to the right of the given range by specifying a large number.
By Header
Identifies a column by pattern matching on its header. Pattern matching makes it possible to locate a column based on only a part of its header. The pattern may be applied to headers found in the first row of the given range, or to headers found in a separately specified named range. Note that in the latter case, it is possible for the found column to be outside of the given range; this is permitted.

Row positions are specified relative to the given range in one of the following ways:

By Index
Identifies a row by its offset relative to the top of the given range. 0 means the first row of the range, 1 means the second row etc. Negative numbers are permitted; for example, -1 means the row just above the given range. It is also permitted to go below the given range by specifying a large number.
By Header
Identifies a row by pattern matching on its row header (this means rows may have headers just like columns often have). Pattern matching makes it possible to locate a row based on only a part of its header. The pattern may be applied to headers found in the first column of the given range, or to headers found in a separately specified named range. Note that in the latter case, it is possible for the found row to be outside of the given range; this is permitted.

How to Enter a Width or Height

A few of the options for the Use property require the width and/or height of the result to be specified. This can be done in several ways (not all are available everywhere):

Same as Range
The width/height will go all the way to the right side/bottom of the given range. Note that this is exactly the same as Relative to Right Side of Range (or Bottom) with an offset of 0.
Relative to Right Side of Range (for the width)
The width will go to the right side of the given range plus/minus the specified number of columns:
If a negative number is specified, columns are dropped from the right side of the given range.
If the number is zero, the width is precisely to the right side of the given range.
If a positive number is specified, extra columns are included beyond the right side of the given range.
Relative to Bottom of Range (for the height)
The height will go to the bottom of the given range plus/minus the specified number of rows:
If a negative number is specified, rows are dropped from the bottom of the given range.
If the number is zero, the height is precisely to the bottom of the given range.
If a positive number is specified, extra rows are included beyond the bottom of the given range.
Fixed Width (or Fixed Height)
The width or height is the specified number of columns or rows, regardless of the width and height of the given range.
To End of Whole Sheet
The width/height will go all the way to the right side/bottom of the sheet.