NExS differentiates between relative, absolute, and indirect references. The latter is unique to NExS.
NExS 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, NExS 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 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:
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.
Certain expressions within the context of NExS require a means to express the current cell. One example is the Find Tool described in section 5.1.5. Other examples include the conditional statistical functions described in Chapter 7, and constraint expressions described in section 6.2.6.
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 NExS where to look, in
relation to the current cell, for the cell being referenced.
The format is as follows:
#{
column offset, row offset}
#{-1}
tells NExS to look to the
column just left of the current cell.
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.