A formula can contain references to other sheets, sometimes called cross-references. 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 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 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, and the topic on Sheet References in a Formula.

Using Code

The following example uses default sheet names in a formula.

Example

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

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

Copyright © GrapeCity, inc. All rights reserved.