Specifying Cell References

Overview

The fpSpread control can use absolute or relative cell references. In absolute cell references, for example, "B2", the column and row specified are the column and row used for calculations. In relative cell references, the column and row used for calculations change based on the location of the formula. For example, if you copy a formula using relative cell references from column B to column C, cell references such as "B2" change to "C2" to reflect the new formula location.

The fpSpread control offers three different cell notations, or cell reference styles, for representing cell addresses. The different cell reference styles default to creating either absolute or relative cell references, but offer some options for either type of cell reference. Be sure to use the cell reference style that best suits your needs, and your users' expectations, if you are allowing users to enter formulas.

The following list describes and gives examples of each cell reference style.

Default cell reference style
The default reference style uses letters and numbers for column and row coordinates, where the letters and numbers represent absolute coordinates. You can use a symbol, the number sign (#), to represent the current column or row as relative coordinates. For example, the reference A1 refers to cell A1; the reference B# refers to the cell in the current row in column B.
"A1" cell reference style
The "A1" reference style uses letters and numbers for column and row coordinates, where the letters and numbers represent relative coordinates, similar to Microsoft Excel cell references. You can use a symbol, the dollar sign ($), to indicate absolute coordinates. For example, if the cell containing the formula is C1, the reference D4 refers to the cell one column over and three rows down. If you wanted to create an absolute reference to cell D4, you can use the notation $D$4.
"R1C1" cell reference style
The "R1C1" reference style uses "R" and number for row, "C" and number for column coordinates, where the notation represents absolute coordinates. You can use square brackets ([ ]) to represent relative coordinates, and the letters "C" or "R" without a number represent the current row or column. For example, R1C1 refers to the cell A1. If you are in cell C1, the reference R[1]C[1] refers to the cell one column to the right and one row down, which is D2. The reference R1C refers to the cell in the first row and the same column as the cell containing the formula.

Notes:

Your formula cannot contain both absolute and relative row or column references. For example, the following formula, which uses the default reference style and adds the values in the first column up to the current row, is invalid.

fpSpread1.Formula = "sum(A1:A#)"

Likewise, the following formula to add the values in the first row up to the current column is invalid.

fpSpread1.Formula = "sum(A1:#1)"

Returning the value of the Formula property provides a string containing the written expression of the formula, for example, SUM(A1:B1). However, if you have changed the cell reference style to a style that cannot represent the formula, the control provides the formula with question marks as placeholders for cell references that cannot be represented.
For example, if you have defined relative cell references used in a formula in cell B1 as RC[-1]+R[-1]C, the formula is interpreted as add the value in the cell to the left (A1) to the value in the cell above ("B0"). The control treats the value in the cell "B0" as an empty cell. If you change the cell reference style to the A1 style, the formula becomes A1+B?, because the A1 style cannot represent cell "B0". However, the control still evaluates the formula as it would using the R1C1 reference style.