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)
 |
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, each worksheet functions as a separate calculation unit with each worksheet having its own calculation settings (auto calculations, iterations, custom functions, custom names, and so on). Changing a calculation setting affects a single worksheet. For this setting of EnableCrossSheetReference, formulas can only reference cells on the same worksheet.
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.
| 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"
|
|