ContextQMD
Back to Devexpress

How to: Use Cell and Worksheet References in Formulas

wpf-16370-controls-and-libraries-spreadsheet-examples-formulas-how-to-use-cell-and-worksheet-references-in-formulas.md

latest2.1 KB
Original Source

How to: Use Cell and Worksheet References in Formulas

This example demonstrates how to create formulas using the R1C1 reference style. To do this, switch on the DocumentSettings.R1C1ReferenceStyle option and assign a formula string containing R1C1 cell references to the CellRange.Formula property. To obtain a cell reference in the R1C1 reference style, you can use the CellRange.GetReferenceR1C1 method.

View Example

csharp
// Switch on the R1C1 reference style in a workbook.
workbook.DocumentSettings.R1C1ReferenceStyle = true;

// Specify a formula with relative R1C1 references in cell D2
// to add values contained in cells A2 through A11.
worksheet.Cells["D2"].Formula = "=SUM(RC[-3]:R[9]C[-3])";

// Specify a formula with absolute R1C1 references 
// to add values contained in cells A2 through A11.
worksheet.Cells["D3"].Formula = "=SUM(R2C1:R11C1)";
vb
' Switch on the R1C1 reference style in a workbook.
workbook.DocumentSettings.R1C1ReferenceStyle = True

' Specify a formula with relative R1C1 references in cell D2
' to add values contained in cells A2 through A11.
worksheet.Cells("D2").Formula = "=SUM(RC[-3]:R[9]C[-3])"

' Specify a formula with absolute R1C1 references 
' to add values contained in cells A2 through A11.
worksheet.Cells("D3").Formula = "=SUM(R2C1:R11C1)"