A formula can contain references to other sheets, sometimes called crossreferences. When a reference to a cell includes a reference to a cell on another sheet, this is called crosssheet referencing. An example of crosssheet referencing in a formula that uses the addition operator would be:
FirstRoundData!A2 + SecondRoundData!A2)
Note: Remember that although most of Spread uses zerobased references to rows and columns, in the creation of formulas you must use onebased references. The column and row numbers start at one (1), not zero (0).
Another example would be keeping a running total off 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.
FpSpread1.Sheets(1).Cells(0,0).Formula = "SUM(Sheet1!A1:Sheet1:A100)"
and 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 can not 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, each worksheet functions as a separate calculation unit with each worksheet having its own calculation settings (auto calculations, iterations, custom functions, custom names, etc). Changing a calculation setting affects a single worksheet. Formulas can only reference cells on the same worksheet.
If the sheet name contains non alphanumeric 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).
For more information on crosssheet referencing, refer to the Formula Reference, and the topic on Sheet References in a Formula.
Return to the overview of Managing Formulas in Cells.
Using Code
The following example uses default sheet names in a formula.
Example
C#  Copy Code 

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

VB  Copy Code 

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