Objective Grid differentiates between relative, absolute, and indirect references. The latter is unique to Objective Grid.
Relative Reference
Objective Grid tracks the referenced cell by considering its position relative to the formula cell, not by its address. For example, if the formula in cell A1 references cell B2, Objective Grid remembers that the referenced cell is one row down and one column right. If you copy the formula in cell A1 to another location (e.g., D17), the formula will reference the cell one row down and one column right of the new location (e.g., E18).
Absolute Reference
Absolute references remain the same, no matter where you move or copy the original formula. For example, if the formula in cell A1 references cell B2, and you copy the formula in cell A1 to another location (e.g. D17), the formula still references cell B2. To specify an absolute cell address, insert a dollar sign ($) before the address coordinate to be fixed, or before both coordinates if the row and column coordinates are to be fixed. For example: $B$2. To specify all or part of a cell address to be absolute, insert a dollar sign ($) before the address coordinate to remain fixed For example:
ü B$5 makes the complete address absolute.
ü $B5 makes the column coordinate (B) absolute, the row coordinate (5) relative.
ü B$5 makes the column coordinate (B) relative, the row coordinate (5) absolute.
Cell ranges are also relative, so when you move a cell range, references in formulas within that range are updated to reflect their new location. To specify an absolute range reference, insert dollar signs ($) before the coordinates in the formula. For example, to make the range A1..D5 absolute, type the reference as $A$1..$D$5. To specify part of a cell range to be absolute, insert dollar signs only before the coordinates to remain absolute. For example, $A1..$D5 will fix the column coordinates of cell references but adjust the row coordinates to reflect the new location.
To reference a cell or range by name:
Type the pre-assigned name of the cell or cell block into the formula.
To assign a name to a cell or range of cells, use the SetRangeName command.
Current Cell Reference
Certain expressions within the context of Objective Grid require a means to express the current cell. Examples include the conditional statistical functions described in "Built-In Worksheet Functions", and constraint expressions described in Section Constraint Expressions. The current cell is identified in any expression with a pound sign (#). References to cells in the neighborhood of the current cell are made with offset values enclosed in braces ( {} ) following the #. The offsets tell Objective Grid where to look, in relation to the current cell, for the cell being referenced.
The format is as follows:
#{column offset, row offset}
ü If you include only one value in the offset, Objective Grid assumes that it is a column offset. For example, the offset reference #{-1} tells Objective Grid to look to the column just left of the current cell.
ü The offset values may be constants
or expressions. Examples:
#{0,-1} refers to the cell above the current
cell.
#{-2} refers to the cell two columns left of the current cell.
#{1}
refers to the cell to the right of the current cell.
#{0,1} refers to the
cell below the current cell.
@CSUM(C4..C100, #{-1} == "Joe") calculates the
sum of all the values in the range C4..C100 for which the cell in the column to
the left contains the string ``Joe.""
@CCOUNT(C4..C100, # #{0,-1}) counts all
the cells in the range C4..C100 whose value is greater than the contents of the
cell immediately above.
@XVALUE("master.xs3", #) returns the value of the
same cell reference in which this function is stored from the sheet
indicated.
/verb/#-1+2/ adds 2 to the cell value from the cell to the
left.