Specifying a Sheet Reference in a Formula

A formula can contain references to other sheets. When a reference to a cell includes a reference to a cell on another sheet, this is called cross-sheet referencing. An example of cross-sheet referencing in a formula that uses the addition operator would be:

(FirstRoundData!A2 + SecondRoundData!A2)

!type=note

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

Another example would be keeping a running total of cells of one sheet on a separate sheet. Use the Formula property to put a formula on one sheet that references the cells you want added from another sheet, as shown in the following code.

FpSpread1.Sheets(1).Cells(0,0).Formula = "SUM(Sheet1!A1:Sheet1:A100)"

Then use the ReferenceStyle property to set the reference style.

You can have formulas that reference other worksheets or you can have automatic calculations at the worksheet level (applies to all sheets). You cannot have both. When EnableCrossSheetReference is True (which is the default setting), the entire workbook acts as a single calculation unit with all worksheets sharing the same calculation settings (auto calculations, iterations, custom functions, custom names, etc). Changing a calculation setting affects all worksheets. Formulas can reference cells on other worksheets. When EnableCrossSheetReference is False, it allows users to set formula reference to a cell in another sheet but the value of the cell will be #REF!.

If the sheet name contains non alpha-numeric characters (for example, a space), then enclose the sheet name in single quotes in the formula. For example, suppose sheet name is "page one" then the formula would be SUM('page one'!$A$1:$A$5).

If the sheet name contains the single quote character, then use two single quote characters in the formula. For example, suppose the sheet name is "scott's page" then the formula would be SUM('scott''s page'!$A$1:$A$5).

If the sheet name contains a colon, then use two single quotes around the sheet name. For example ("'Sheet:name'!$B$1:$F$1").

For more information on cross-sheet referencing, refer to the Formula Reference.

Using Code

The following example uses default sheet names in a formula.

Example

This example sets the formula.

fpSpread1.Sheets[0].Cells[0,0].Formula = "Sheet1!A3 + Sheet2!A2";

fpSpread1.Sheets(0).Cells(0,0).Formula = "Sheet1!A3 + Sheet2!A2"

See Also

Formulas in Cells

Placing a Formula in Cells

Specifying a Cell Reference in a Formula

Specifying an External Reference in a Formula

Using a Circular Reference in a Formula

Nesting Functions in a Formula

Recalculating and Updating Formulas Automatically

Finding a Value Using GoalSeek

Allowing the User to Enter Formulas

Creating and Using a Custom Name

Creating and Using a Custom Function

Creating and Using External Variable

Using the Array Formula

Working with the Formula Text Box

Setting up the Name Box

Using Language Package

Accessing Data from Header or Footer

Managing External Reference

Working With Dynamic Array Formulas