Besides values, operators, and functions, a formula can contain references to values in other cells. 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 which type of cell reference for the sheet by using the ReferenceStyle property. For details on the way to specify the reference style, refer to the ReferenceStyle property of the SheetView class, and the ReferenceStyle enumeration.

If you have changed the cell reference style to a style that cannot represent the formula, the Spread 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:

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: 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).

Range Reference

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).

Either both row coordinates must be absolute or both row coordinates must be relative. For example:

Develop your formulas so that either both row coordinates are absolute,

    for (int i = 0; i < n; i++)

    fpspread1.Sheets[0].Cells[i,column].Formula = "SUM(R1C[-1]:R" +         (i+1).ToString() + "C[-1])"

or both row coordinates are relative,

    for (int i = 0; i < n; i++)

    fpspread1.Sheets[0].Cells[i,column].Formula = "SUM(R[" +         (-i).ToString() + "]C[-1]:RC[-1])"

The same restrictions apply to start column and end column coordinates.

For more information on cell reference styles, refer to the Formula Reference.

Using the Properties Window

1. At design time, in the Properties window select the Sheets property and click on the button to open the SheetView Collection editor.
2. Select the sheet from the Member list.
3. In the properties list (in the Calculation category), select the ReferenceStyle property.
4. Click the drop-down arrow to display the choices and select the value, either A1 or R1C1.

Using Code

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

Example

This example sets the reference style.

fpSpread1.Sheets[0].ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
fpSpread1.Sheets[0].Cells[2, 2].Formula = "SUM(A1:A6)"; 

FpSpread1.Sheets(0).ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1
FpSpread1.Sheets(0).Cells(2, 2).Formula = "SUM(A1:A6)" 

Using the Spread Designer

1. Select the sheet tab name for the sheet.
2. In the property list (in the Calculation category), select the ReferenceStyle property.
3. Click the drop-down arrow to display the choices and select the value, either A1 or R1C1.
4. From the File menu choose Apply and Exit to apply your changes to the component and exit Spread Designer.

Copyright © GrapeCity, inc. All rights reserved.