Implement Excel-Like Formula References and Named Ranges in .NET Spreadsheet Applications

The most powerful feature of any spreadsheet is the formula reference - the syntax for referring to another cell or range of cells in a formula as a simple variable in the formula expression. Formula references make it possible to refer to another cell, range, worksheet, workbook, named range, or external variable directly in a formula expression.

In this tutorial, we will use the WinForms spreadsheet component, Spread.NET, which supports the same major formula reference types as Microsoft Excel, and allows external variables to integrate spreadsheet calculations with data from your .NET application. This article explains each reference type and shows how to use formula references, named ranges, and external variables in C# and VB.NET WinForms spreadsheet applications.

Download the Latest Release of Spread.NET Today!

Working with Formula References and Named Ranges in .NET

• Reference Styles
• Cell and Range References
  • Relative References
  • Absolute References
  • Mixed References
• Worksheet References
  • Three-D References
  • Workbook References (External References)
• Structured References to Table Cells
• Custom Names for Cell Ranges and More
• Custom Name Scope: Workbook and Worksheet Names
• Evaluation of Custom Name Expressions
• Absolute and Relative References in Custom Names
• External Variables
• Conclusion

Download a Finished .NET WinForms Sample Application to Follow Along: C# or VB.NET.

Reference Styles

Spread.NET and Microsoft Excel support two reference styles: A1 and R1C1. A1 is the default style. It identifies the column first with a letter, followed by the row number. In Spread.NET, you can set the reference style in C# or VB.NET by using the ReferenceStyle property:

C#:

fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.ReferenceStyle = GrapeCity.Spreadsheet.ReferenceStyle.R1C1;

VB:

FpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.ReferenceStyle = GrapeCity.Spreadsheet.ReferenceStyle.R1C1

Some examples of references using A1 reference style are “A1”, which refers to the upper-left cell, and “E5”, which refers to the cell in the fifth row and fifth column.

R1C1 can be easier for programming scenarios because it uses numeric indexes for both rows and columns. The row number comes first, followed by the column number. If you switch a sheet to R1C1, consider updating the column headers to display numbers instead of letters.

In Spread.NET, use the ColumnHeaderAutoText property to set the HeaderAutoText value:

C#:

foreach (SheetView sv in fpSpread1.Sheets)  
            sv.ColumnHeaderAutoText = HeaderAutoText.Numbers;

VB:

For Each sv As SheetView In FpSpread1.Sheets  
            sv.ColumnHeaderAutoText = HeaderAutoText.Numbers  
        Next

Cell and Range References

The most basic, and most commonly encountered type of reference in formulas, is the cell or range reference. A cell reference specifies a single cell. The syntax for a cell reference in the formula depends on the reference style.

A cell range reference uses a special operator ‘:’ called the range operator – the range operator is used between the upper-left cell reference and the lower-right cell reference to specify the cell range. A reference to a single cell generally does not use the range operator, but it is perfectly legal to specify a cell range reference with the same start and end cells.

Relative References

Relative references are the default formula reference type. They are called relative because they are evaluated from the cell that contains the formula. Internally, they are stored as offsets from the formula cell: for example, “the cell two rows above and one column to the right.” When you copy, paste, or drag-fill a formula, relative references automatically adjust to the new location.

Relative references are generally more convenient to use because the built-in adjustment of the referenced cells is typically very useful when drag-filling formulas to calculate the same formula across a row or down a column. When you want to do the same calculation in a range of cells, and the referenced cells for the calculation are in parallel ranges, then relative references are appropriate.

Examples of typical use cases for relative references include most tabular data, such as monthly sales by division or product, as well as subtotals and totals in a balance sheet or financial report.

Absolute References

Absolute references are fixed, or “hard-coded” references to the cell or cell range that do not depend on the target cell. Absolute references are specified in A1 reference style using the ‘$’ prefix, and in the R1C1 reference style using explicit row and column numbers. Some examples of absolute references are:

Absolute references are best when a formula must always point to the same cell or range. If you find yourself repeatedly using an absolute reference, consider creating a custom name for that cell or range instead. Named ranges make formulas easier to read, audit, and maintain.

Mixed References

Mixed references combine relative and absolute behavior. Each row and column coordinate can be locked or allowed to adjust independently. This flexibility makes mixed references useful for building formulas that can be copied across rows or columns while keeping part of the reference fixed, such as formulas for cumulative totals or probability tables.

Mixed references are very useful for some specific use cases. For example, let’s say you want to create a formula to calculate the cumulative total of the values in another column. You can use the SUM function with a mixed cell range reference. Specify the starting cell reference with an absolute cell reference to the cell containing the first value in the other column, and specify the end cell reference with a relative row and an absolute column:

When the formula is copied to other cells in the column using copy/paste or drag-fill, only the last row reference changes, which computes the cumulative total.

Worksheet References

Worksheet references are references to another worksheet in the same workbook. The worksheet reference uses the name of the worksheet which is displayed in the worksheet tab. If the name contains spaces, then it must be enclosed in single quotes. Worksheet references are required when referencing a cell or cell range in another worksheet, or when referencing a cell or cell range in the formula for a custom name with workbook scope – that will be covered in more detail below in the section about Custom Names.

When referencing a cell or cell range in the same worksheet, it is not necessary to use the worksheet name – you can reference cells and cell ranges within the same worksheet without specifying the worksheet reference.

If you do specify the workbook reference, then when the cell is copied and pasted to another worksheet, the worksheet reference will be included and will refer to the originally specified cell or cell range. For example, if there is a formula “=Sheet1!$A$1” in the cell Sheet1!E1, and this formula is copied and pasted to Sheet2, then the cell in Sheet2 will continue to reference Sheet1!$A$1 because the worksheet reference is copied as part of the formula.

If the formula were “=$A$1” then the cell in Sheet2 would instead reference the cell Sheet2!$A$1 because there is no worksheet reference specified in the formula. Some examples of formulas containing worksheet references are:

Note that when the worksheet name contains spaces or other special characters, or when it might be confused with a cell range reference in A1 or R1C1 reference style, then the worksheet name reference must be enclosed in single quotes. If the worksheet name contains single quotes or apostrophes, then those must be doubled inside the formula string – for example, if a worksheet named “test’s results” is used in a formula, then the reference must be as follows: ‘test’’s results’!$A$1

Three-D References

Worksheet references can also use the range operator ‘:’ to create a three-D reference. A three-D reference specifies the starting and ending worksheets and includes the specified cell or cell range in each of those worksheets. Some examples of formulas containing three-D references are:

Workbook References (External References)

Workbook references are references to another workbook. These are also sometimes called “external references” because the source is external to the workbook and might not be available, so a cached last-known value is kept in the workbook for use in calculations when the source workbook is not open.

External references are also called “links” because the external workbooks are linked to the workbook through the formulas containing the external references to the other workbook. External references use the workbook name or file name enclosed in square brackets.

Some examples of formulas containing external workbook references are:

The API’s in Spread.NET related to external references include IWorkbook.LinkSources, IWorkbook.SaveLinkValues, IWorkbook.UpdateLinks, IWorkbook.BreakLink, and IWorkbook.UpdateLink, and full details about external reference support in Spread.NET are here.

Structured References to Table Cells

Structured references use readable table and column names instead of standard cell coordinates. They can make formulas easier to understand in C# and VB.NET spreadsheet applications, especially when working with Excel-style tables. Structured references can be used in table formulas and in formulas that refer to table cells or ranges. When you create a table from a worksheet range, the top row becomes the table header.

Table columns must have names. If the top row does not already contain column names, Spread.NET generates default names such as Column1 and Column2:

Structured references use a special syntax that is much easier to read and understand because they use column names. There are also predefined names for accessing cells in the table header row and the totals row.

Some examples of formulas containing structured references are:

• [@Column1]+[@Column2]
• [Column1]
• Table1[[#Headers],[Column1]]
• Table1[[#Totals],[Column1]]
• Table1[#All]

The first two examples above do not include a table name reference and are only valid within the table cells that reference their columns. The last three examples do include the table name and can be used in cells outside of the table. The ‘@’ specifies a relative reference to the table cells in “this row” in the specified column. The column names can contain spaces and do not need to be enclosed in single quotes – for example, if a column is named “January Sales”, then you can reference it as “[@January Sales]” in a table cell formula.

Table cells cannot contain array formulas; however, array formulas in non-table cells can use structured references to ranges of table cells. Structured references to external workbooks use a special syntax that looks like a worksheet reference, but uses the table name instead of a worksheet name.

Some examples of formulas containing structured references to external workbooks are:

• Book2!Table1[[#Headers],[Column1]]
• ‘Sales Data.xlsx’!Table1[[#Totals],[Column1]]
• 'Tax Tables.xlsx’!Table1[#All]

Custom Names for Cell Ranges and More

Named ranges and custom names can make formulas much easier to read. Instead of using explicit cell or range references throughout a workbook, you can define descriptive names and use those names in formulas. A valid name must start with a letter, underscore, or backslash and can contain only letters, numbers, periods, and underscores.

The name cannot contain embedded space characters and cannot look like a cell reference – for example, “D$5” or “R54C” would not be permitted as these might be confused with range references.

You also cannot use “c” or “r” alone as a name (these are reserved). Names are not case-sensitive in formulas, but you can use naming conventions like camel-casing to initialize the names and improve readability in the Name Manager tool.

Custom names are not limited to named cells and ranges – you can create a custom name for any valid formula expression. All calculation functions are available for use in custom name formulas. Very large and complex calculations can often be broken down into simpler steps with custom names, making the formulas much easier to read and understand.

Very complex calculations can be broken down into concrete stages with custom names. This is especially helpful in cases where some of those concrete stages can be reused across multiple calculations – for example, by using a custom name for tax rate calculations. Custom names also support specifying a comment to document the use and purpose of the name, which the user can see when referencing the name in the formula bar or in the Name Manager tool:

When adding a workbook-scope name, you can use a special syntax with the worksheet reference missing, but still using the ‘!’ operator before the cell or cell range reference. This special syntax references a cell or cell range in the active sheet when the name is used in a formula.

The Name Manager tool shown above is built into the main spreadsheet control binary and available programmatically using simple code:

C#:

fpSpread1.ShowListCustomNameForm(this);

VB:

FpSpread1.ShowListCustomNameForm(Me)

Custom Name Scope: Workbook and Worksheet Names

Custom names can be scoped to either the workbook or the worksheet. This is specified when creating the name – if you are using the user interface, then you can select the scope using the drop-down:

In code, you can specify the scope of the name in the last argument when calling the INames.Add method:

C#:

fpSpread1.AsWorkbook().Names.Add("test", "!$A$1", null, 0, 0, true);

VB:

FpSpread1.AsWorkbook().Names.Add("test", "!$A$1", Nothing, 0, 0, True)

Workbook-level names are visible to all worksheets in the workbook. In cell formulas, Worksheet-level names defined for that worksheet take precedence over workbook-level names when the same name is defined for both levels, and worksheet-level names are only visible to the cells of that worksheet.

When a worksheet is copied, all its worksheet-level custom names are also copied to new worksheet-level custom names in the new worksheet.

Evaluation of Custom Name Expressions

When you add or change a custom name, the specified formula is immediately parsed into a logical expression that represents that formula, and any cells containing formulas using that name will recalculate automatically (if automatic calculation isn’t disabled). When a cell formula contains that name, the formula is calculated as if the name expression replaced the name reference, and all references and functions (for example, ROW()) are evaluated in the context of the particular cell being calculated.

Custom name expressions are not actually calculated until some cell formula references that name and that cell is calculated. The Name Manager calculates custom name expressions to display the Value column of the dialog. In that case, the values are calculated using the current active cell as the context for relative references.

Absolute and Relative References in Custom Names

Custom names can contain absolute or relative references just like cell formulas. Generally, it is better to use absolute references in custom names, so those names can be used in any cell and always reference the specified absolute references. Custom names that contain relative references are much more complicated to use, because their formulas are necessarily tied to some cell on creation and can only be used in cells where those relative references make sense.

When creating the custom name using the UI “New Name” dialog, the active cell in the active sheet is used to parse the relative references in the formula expression, so all relative references are parsed into relative offsets from that cell – for example, “the cell two rows up and one column to the left” – and when this name is used in a different cell, the referenced cell changes too.

It is sometimes helpful to use relative references in custom names, but in general should be avoided unless necessary. When they are used, it is important to note in the comment for the custom name which cell should be active when viewing or editing the formulas using the Name Manager tool, because when other cells are active, the formulas will not show or calculate as expected.

External Variables

Spread.NET Windows Forms also supports external variables, a powerful way to connect spreadsheet formulas with data outside the workbook in C# and VB.NET applications. External variables behave like custom names, but they can push data from an external source into the calculation engine or pull calculated values back into your .NET application.

That “external source” can be anything the program requires, like a web service or another control – it is up to the application to implement. The external variable has a name and a formula expression which can reference any worksheet like a workbook-scope custom name. The calculation of external variables operates much differently than that of custom names: external variables operate like tiny one-cell worksheets that are separate from the other worksheets.

Relative references are not supported for external variable expressions because there is no context cell when those expressions are evaluated, so all relative references are automatically converted into absolute references.

Since the external variable operates at workbook scope, all cell range references must specify the worksheet name(s).

External Variable Example in C# and VB.NET

Creating external variables in C# and VB.NET requires implementing a class that inherits from ExternalVariable. This simple example shows how to create an external variable that takes a value from a TextBox control for use in calculations, and then create another external variable that multiplies the first value by a factor in a cell and returns the result in another TextBox control.

Here is the implementation of the class:

C#:

using System;  
using System.Windows.Forms;  
using GrapeCity.CalcEngine;  
using GrapeCity.Spreadsheet;

namespace ExternalVariablesDemoCS  
{  
    // Creating and defining the external variable  
    public class TextBoxExternalVariable : ExternalVariable  
    {  
        private TextBox _textBox;  
        private bool _asInput;  
        public TextBoxExternalVariable(TextBox textBox, bool asInput)  
        {  
            _textBox = textBox;  
            if (asInput)  
            {  
                textBox.TextChanged += TextBox_TextChanged;  
            }  
        }  
        private void TextBox_TextChanged(object sender, EventArgs e)  
        {  
            Dirty();  
        }
        protected override bool OnDirtying()  
        {  
            return !_asInput;  
        }
        protected override void OnDirtied()  
        {  
            Refresh();  
        }
        protected override void EvaluateCore(IEvaluationContext context,  
         IValue result)  
        {  
            string text = _textBox.Text;  
            if (!string.IsNullOrEmpty(text) && double.TryParse(text,  
                 out double dblValue))  
            {  
                result.SetValue(dblValue);  
            }  
            else  
            {  
                result.SetValue(text);  
            }  
        }
        public void Refresh()  
        {  
            if (!_asInput)  
            {  
                _textBox.Text = this.Value.GetText();  
            }  
        }  
    }  
}

VB:

Imports GrapeCity.Spreadsheet  
Imports GrapeCity.CalcEngine


'Creating and defining the external variable  
Public Class TextBoxExternalVariable  
    Inherits ExternalVariable  
    Private _textBox As TextBox  
    Private _asInput As Boolean  
    Public Sub New(ByVal textBox As TextBox, ByVal asInput As Boolean)  
        _textBox = textBox  
        If asInput Then  
            AddHandler textBox.TextChanged, AddressOf TextBox_TextChanged  
        End If  
    End Sub  
    Private Sub TextBox_TextChanged(ByVal sender As Object, ByVal e As EventArgs)  
        Dirty()  
    End Sub  
    Protected Overrides Function OnDirtying() As Boolean  
        Return Not _asInput  
    End Function  
    Protected Overrides Sub OnDirtied()  
        Refresh()  
    End Sub  
    Protected Overrides Sub EvaluateCore(ByVal context As IEvaluationContext, ByVal result As IValue)  
        Dim text As String = _textBox.Text  
        Dim dblValue As Double = Nothing  
        If Not String.IsNullOrEmpty(text) AndAlso Double.TryParse(text, dblValue) Then  
            result.SetValue(dblValue)  
        Else  
            result.SetValue(text)  
        End If  
    End Sub  
    Public Sub Refresh()  
        If Not _asInput Then  
            _textBox.Text = Me.Value.GetText()  
        End If  
    End Sub  
End Class

The constructor of TextBoxExternalVariable requires a TextBox control and a flag to indicate whether the text box will be used for input or for output. The TextBox control is kept in a local field, and if the input flag is set, an event handler is attached to the TextBox.TextChanged event is used to call the Dirty() method when the text changes.

The code in OnDirtying() returns true when the variable is used for input and ignored otherwise, and the code in OnDirtied() calls the Refresh() method, which updates the TextBox.Text with the current value when the input flag is not set.

There is some initialization code required in the form to set up the sample:

C#:

using System;  
using System.Windows.Forms;

namespace ExternalVariablesDemoCS  
{  
    public partial class Form1 : Form  
    {  
        public Form1()  
        {  
            InitializeComponent();  
        }  
        private void Form1_Load(object sender, EventArgs e)  
        {  
            var workbook = fpSpread1.AsWorkbook();  
            var activeSheet = workbook.ActiveSheet;  
            activeSheet.Cells["A1"].Value = "Factor";  
            activeSheet.Cells["B1"].Value = 2;
            // Adding Ext. Variable - x referring to textbox1  
            workbook.Names.AddExternalVariable("x",  
            new TextBoxExternalVariable(textBox1, true));
// Adding Ext. Variable "y" referring to textbox2 with formula "Sheet1!B1 * x"  
            var extVariable2 = new TextBoxExternalVariable(textBox2, false);  
            workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x");
            extVariable2.Refresh();  
        }  
    }  
}

VB:

Public Class Form1  
    'Using the external variable with text box control  
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load  
        Dim workbook = FpSpread1.AsWorkbook()  
        Dim activeSheet = workbook.ActiveSheet  
        activeSheet.Cells("A1").Value = "Factor"  
        activeSheet.Cells("B1").Value = 2  
        workbook.Names.AddExternalVariable("x", New TextBoxExternalVariable(TextBox1, True))  
        Dim extVariable2 = New TextBoxExternalVariable(TextBox2, False)  
        workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x")  
        extVariable2.Refresh()  
    End Sub  
End Class

The first external variable named “x” is created using the first text box and used for input. The second external variable named “y” is created using the second text box for output, and calculates the product of the first value and the value in Sheet1!B1. This pattern works the same way in C# and VB.NET Spread.NET applications.

At runtime in C# and VB.NET Spread.NET applications, when the value in the first text box changes and raises the TextChanged event, the second text box updates immediately with the calculated product.

Download the Finished Sample Applications Here: C# or VB.NET

Conclusion

Formula references, named ranges, structured references, and external variables give you flexible ways to build clearer, more maintainable spreadsheet logic in Spread.NET WinForms applications. By understanding when to use relative, absolute, mixed, worksheet, workbook, and table-based references, you can create formulas that behave predictably as users copy, fill, and reorganize data. And with external variables, your C# and VB.NET applications can connect workbook calculations directly to application data, making Spread.NET a practical foundation for dynamic, Excel-like business solutions in .NET.

Download a Trial of this .NET Spreadsheet Component Today!