Setting Formula for Named Range

Setting Formula for Named Range

Like Excel application, Aspose.Cells for Python via .NET APIs provide the ability to specify a formula for a named range while using its Range.refers_to property. There could be numerous usability scenarios for this feature, a few of which are detailed as follow.

Setting a Simple Formula for Named Range

A simple formula could be a reference to another cell in the same (or different) worksheet. The following example creates a named range in a new spreadsheet and sets its reference to another cell.

from aspose.cells import Workbook


#  The path to the documents directory.
dataDir = "./"
#  Create an instance of Workbook
book = Workbook()
#  Get the WorksheetCollection
worksheets = book.worksheets
#  Add a new Named Range with name "NewNamedRange"
index = worksheets.names.add("NewNamedRange")
#  Access the newly created Named Range
name = worksheets.names[index]
#  Set RefersTo property of the Named Range to a formula. Formula references another cell in the same worksheet
name.refers_to = "=Sheet1!$A$3"
#  Set the formula in the cell A1 to the newly created Named Range
worksheets[0].cells.get("A1").formula = "NewNamedRange"
#  Insert the value in cell A3 which is being referenced in the Named Range
worksheets[0].cells.get("A3").put_value("This is the value of A3")
#  Calculate formulas
book.calculate_formula()
#  Save the result in XLSX format
book.save(dataDir + "output_out.xlsx")

Setting a Complex Formula for Named Range

A complex formula could be anything such as a dynamic range or a formula spanning over multiple cells in different worksheets. The following example creates a dynamic range using the INDEX function to get the value from a list based on its location.

from aspose.cells import Workbook


#  The path to the documents directory.
dataDir = "./"
#  Create an instance of Workbook
book = Workbook()
#  Get the WorksheetCollection
worksheets = book.worksheets
#  Add a new Named Range with name "data"
index = worksheets.names.add("data")
#  Access the newly created Named Range from the collection
data = worksheets.names[index]
#  Set RefersTo property of the Named Range to a cell range in same worksheet
data.refers_to = "=Sheet1!$A$1:$A$10"
#  Add another Named Range with name "range"
index = worksheets.names.add("range")
#  Access the newly created Named Range from the collection
range = worksheets.names[index]
#  Set RefersTo property to a formula using the Named Range data
range.refers_to = "=INDEX(data,Sheet1!$A$1,1):INDEX(data,Sheet1!$A$1,9)"
#  Save the workbook
book.save(dataDir + "output_out.xlsx")

Here is another example that uses a named range to sum values from 2 cells in different worksheets.

from aspose.cells import Workbook


#  The path to the documents directory.
dataDir = "./"
#  Create an instance of Workbook
book = Workbook()
#  Get the WorksheetCollection
worksheets = book.worksheets
#  Insert some data in cell A1 of Sheet1
worksheets.get("Sheet1").cells.get("A1").put_value(10)
#  Add a new Worksheet and insert a value to cell A1
worksheets[worksheets.add()].cells.get("A1").put_value(10)
#  Add a new Named Range with name "range"
index = worksheets.names.add("range")
#  Access the newly created Named Range from the collection
range = worksheets.names[index]
#  Set RefersTo property of the Named Range to a SUM function
range.refers_to = "=SUM(Sheet1!$A$1,Sheet2!$A$1)"
#  Insert the Named Range as formula to 3rd worksheet
worksheets[worksheets.add()].cells.get("A1").formula = "range"
#  Calculate formulas
book.calculate_formula()
#  Save the result in XLSX format
book.save(dataDir + "output_out.xlsx")