<definedName> (Defined Name)

This element defines the defined names that are defined within this workbook. Defined names are descriptive text that is used to represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.

A defined name in a formula can make it easier to understand the purpose of the formula. For example, the formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(C20:C30).

Names are available to any sheet. For example, if the name ProjectedSales refers to the range A20:A30 on the first worksheet in a workbook, you can use the name ProjectedSales on any other sheet in the same workbook to refer to range A20:A30 on the first worksheet.

Names can also be used to represent formulas or values that do not change (constants). For example, you can use the name SalesTax to represent the sales tax amount (such as 6.2 percent) applied to sales transactions.

You can also link to a defined name in another workbook, or define a name that refers to cells in another workbook. For example, the formula =SUM(Sales.xls!ProjectedSales) refers to the named range ProjectedSales in the workbook named Sales.

A compliant producer or consumer will consider a defined name in the range A1-XFD1048576 to be invalid.

All other names outside this range can be defined as names and will override a cell reference if an ambiguity exists.

For clarification: LOG10 is always a cell reference, LOG10() is always formula, LOGO1000 can be a defined name that overrides a cell reference.

Parent Elements

<definedNames> (§3.2.6)

Attributes

Description

<comment> (Comment)

Specifies the comment the user provided when the name was created.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<customMenu> (Custom Menu Text)

Specifies custom menu text for the defined name.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<description> (Description)

Specifies description text for the defined name.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<function> (Function)

Specifies a boolean value that indicates that the defined name refers to a user-defined function. This attribute is used when there is an add-in or other code project associated with the file.

A value of on, 1, or true indicates the name refers to a function.

A value of off, 0, or false indicates the name does not refer to a function.

The default value for this attribute is false.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<functionGroupId> (Function Group Id)

Specifies the function group index if the defined name refers to a function. The function group defines the general category for the function. This attribute is used when there is an add-in or other code project associated with the file.

The following functionGroupIds are defined in SpreadsheetML for applications that support the association of an add-in or code project for their workbook:

  • 1Financial

  • 2Date and Time

  • 3Math and Trig

  • 4Statistical

  • 5Lookup and Reference

  • 6Database

  • 7Text

  • 8Logical

  • 9Information

  • 10Commands

  • 11Customizing

  • 12Macro Control

  • 13DDE / External

  • 14User Defined

  • 15Engineering

  • 16Cube

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

<help> (Help)

Specifies the help topic to display for this defined name.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<hidden> (Hidden Name)

Specifies a boolean value that indicates whether the defined name is hidden in the user interface.

A value of on, 1, or true indicates the name is hidden.

A value of off, 0, or false indicates the name is not hidden.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<localSheetId> (Local Name Sheet Id)

Specifies the sheet index in this workbook where data from an external reference is displayed.

In the following example, the defined name refers to a range whose data source is an external database called “Northwind_Database”:

example: 
<definedName name="Northwind_Database"
  localSheetId="2">Sheet5!$A$1:$T$47</definedName>

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

<name> (Defined Name)

Specifies the name that will appear in the user interface for the defined name. This attribute is required. The following built-in names are defined in this SpreadsheetML specification:

  • Print

  • _xlnm .Print_Area: this defined name specifies the workbook's print area.

  • _xlnm .Print_Titles: this defined name specifies the row(s) or column(s) to repeat at the top of each printed page.

  • Filter & Advanced Filter

  • _xlnm .Criteria: this defined name refers to a range containing the criteria values to be used in applying an advanced filter to a range of data.

  • _xlnm ._FilterDatabase: can be one of the following

    1. this defined name refers to a range to which an advanced filter has been applied. This represents the source data range, unfiltered.

    2. This defined name refers to a range to which an AutoFilter has been applied.

  • _xlnm .Extract: this defined name refers to the range containing the filtered output values resulting from applying an advanced filter criteria to a source range.

  • Miscellaneous

  • _xlnm .Consolidate_Area: the defined name refers to a consolidation area.

  • _xlnm .Database: the range specified in the defined name is from a database data source.

  • _xlnm .Sheet_Title: the defined name refers to a sheet title.

Built-in names reserved by SpreadsheetML begin with "_xlnm.". End users shall not use this string for custom names in the user interface.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<publishToServer> (Publish To Server)

Specifies a boolean value that indicates whether the defined name is included in the version of the workbook that is published to or rendered on a Web or application server.

A value of on, 1, or true indicates the name shall be published.

A value of off, 0, or false indicates the name shall not be published.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<shortcutKey> (Shortcut Key)

Specifies the keyboard shortcut for the defined name.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<statusBar> (Status Bar)

Specifies text that is displayed on the application status bar when the user places focus on the defined name.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<vbProcedure> (Procedure)

Specified a  boolean value that indicates whether the defined name is related to an external function, command, or other executable code.

A value of on, 1, or true indicates the name is related to an external function, command, or other executable code, and the loading application can optionally decide whether to load and/or execute the commands.

A value of off, 0, or false indicates the name does not refer to an external function, command, or other executable code.

The default value for this attribute is false.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<workbookParameter> (Workbook Parameter (Server))

Specifies a boolean value that indicates that the name is used as a workbook parameter on a version of the workbook that is published to or rendered on a Web or application server.

A value of on, 1, or true indicates is a workbook parameter on the application server.

A value of off, 0, or false indicates is not a workbook parameter on the application server.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<xlm> (External Function)

Specifies a boolean value that indicates whether the defined name is related to an external function, command, or other executable code.

A value of on, 1, or true indicates the name is related to an external function, command, or other executable code, and the loading application can optionally decide whether to load and/or execute the commands.

A value of off, 0, or false indicates the name does not refer to an external function, command, or other executable code.

The default value for this attribute is false.

The possible values for this attribute are defined by the XML Schema boolean datatype.

The following XML Schema fragment defines the contents of this element:

<complexType name="CT_DefinedName">
	<simpleContent>
	<extension base="ST_Formula">
	<attribute name="name" type="ST_Xstring" use="required"/>
	<attribute name="comment" type="ST_Xstring" use="optional"/>
	<attribute name="customMenu" type="ST_Xstring" use="optional"/>
	<attribute name="description" type="ST_Xstring" use="optional"/>
	<attribute name="help" type="ST_Xstring" use="optional"/>
	<attribute name="statusBar" type="ST_Xstring" use="optional"/>
	<attribute name="localSheetId" type="xsd:unsignedInt" use="optional"/>
	<attribute name="hidden" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="function" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="vbProcedure" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="xlm" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="functionGroupId" type="xsd:unsignedInt" use="optional"/>
	<attribute name="shortcutKey" type="ST_Xstring" use="optional"/>
	<attribute name="publishToServer" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="workbookParameter" type="xsd:boolean" use="optional" default="false"/>
	</extension>
	</simpleContent>
</complexType>