Spread 8.0 Documentation
Specifying Cell References
Support Options
User's Guide > Working with the Control > Working with Data > Entering Formulas > Specifying Cell References

Glossary Item Box

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.

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.

© 2002-2014 ComponentOne, a division of GrapeCity. All Rights Reserved.