Specifying a Cell Reference Style in a Formula

In This Topic

Besides values, operators, and functions, a formula can contain references to values in other cells or sheets. For example, to find the sum of the values in two cells, the formula can refer to the cell coordinates by row and column. You can use an absolute cell reference (with the actual coordinates of the row and column) or a relative cell reference (with the coordinates relative to the current cell). You choose the type of cell reference for the sheet by using the ReferenceStyle property. Spread does not support range references where the start row and end row consist of different reference types (for example, one absolute coordinate and one relative coordinate). For details on the way to specify the reference style, refer to the ReferenceStyle enumeration and the SheetView ReferenceStyle property in the Assembly Reference.

If you have changed the cell reference style to a style that cannot represent the formula, the component provides the formula with question marks as placeholders for cell references that cannot be represented.

The following table contains examples of valid formulas using references:

Function	Description
SUM(A2:A10)	Sums rows 2 through 10 in the first column
PI( )*C6	Pi times the value in cell C6
(A3 + B3) * C3	Adds the values in the first two cells of row 3 and multiplies the result by the value in the third cell
IF(A4>5, A42, A43)	If the contents of cell A4 are greater than 5, then multiply the contents of cell A4 by 2, or else multiply the contents of cell A4 by 3

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 component 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 component still evaluates the formula as it would using the R1C1 reference style.

Note: Remember that although most of Spread uses zero-based references to rows and columns, in the creation of formulas you must use one-based references. The column and row numbers start at one (1), not zero (0).

For more information on cell reference styles, refer to the Formula Reference, and the topic Cell References in a Formula.

Using Code

Specify the reference style by setting the ReferenceStyle property or use the default ReferenceStyle value.

Example

This example sets the reference style.

C#	Copy Code
FpSpread1.Sheets[0].ReferenceStyle = FarPoint.Web.Spread.Model.ReferenceStyle.A1;

VB	Copy Code
FpSpread1.Sheets(0).ReferenceStyle = FarPoint.Web.Spread.Model.ReferenceStyle.A1

Using the Spread Designer

Select the Settings menu.
Select the Calculation icon under the Sheet Settings section.
Set the various formula related properties.
Select OK to close the dialog.
Click Apply and Exit to close the Spread Designer.