[ms-xlsx]: Excel (. xlsx) Extensions to the Office Open xml spreadsheetml file Format Intellectual Property Rights Notice for Open Specifications Documentation

1264.1.1CT_DataValidationFormula

Target namespace: http://schemas.microsoft.com/office/spreadsheetml/2009/9/main

Referenced by: CT_DataValidation

CT_DataValidationFormula is a complex type that specifies a formula used in data validation.

Child Elements:

xm:f: An f element (section 2.4.3) that specifies a formula for the data validation. The formula MUST adhere to the grammar provided in section 2.2.2, with the following restrictions:

• 
  MUST NOT use the ref-infix-operator, local-cell-reference, bang-reference, bang-name, array-constant, sheet-range-reference, or structure-reference production rules.
  
• 
  MUST be an external-cell-reference if used by the formula1 element of the ancestor CT_DataValidation element (section 2.6.5), and the formula1 element of the ancestor CT_DataValidation element uses the external-cell-reference production rule, and the type attribute of the ancestor CT_DataValidation element is "list".
  
• 
  MUST NOT be an external-cell-reference that references more than one cell if used by the formula1 element of the ancestor CT_DataValidation element and the type attribute of the ancestor CT_DataValidation element is not "list".
  
• 
  MUST NOT be an external-cell-reference that references more than one cell if used by the formula2 element of the ancestor CT_DataValidation element.
  

The following W3C XML Schema ([XMLSCHEMA1] section 2.1) fragment specifies the contents of this complex type.

1265

1266

1267

1268

1269

See section 5.4 for the full W3C XML Schema ([XMLSCHEMA1] section 2.1).

1269.1.1CT_DataValidation

Target namespace: http://schemas.microsoft.com/office/spreadsheetml/2009/9/main

Referenced by: CT_DataValidations

CT_DataValidation is a complex type that specifies data validation for a range on this sheet.

Child Elements:

formula1: A CT_DataValidationFormula element (section 2.6.4) that specifies the first formula for the data validation.

• 
  If operator is "between" or "notBetween" and type is not "custom", "list", or "none", this formula is used as the lesser of two bounding values and MUST exist.
  
• 
  If operator is not "between" or "notBetween", or type is "custom", this formula is the only formula and MUST exist.
  
• 
  If operator is not "between" or "notBetween", or type is "custom" or "list", either formula is the only formula and MUST exist or CT_DataValidation MUST have a list (section 2.4.66) child element.
  
• 
  If the type is "none", this formula MUST NOT exist.
  

formula2: A CT_DataValidationFormula element that specifies the second formula for the data validation.

• 
  If operator is "between" or "notBetween" and type is not "custom", "list", or "none", this formula is used as the greater of two bounding values and MUST exist.
  
• 
  If operator is not "between" or "notBetween", or type is "custom", "list", or "none", this formula MUST NOT exist.
  

xm:sqref: A sqref element (section 2.4.5) that specifies ranges to which data validation is applied.

Attributes:

type: An ST_DataValidationType ([ISO/IEC29500-1:2012] section 18.18.21) attribute that specifies the type of data validation.

errorStyle: An ST_DataValidationErrorStyle ([ISO/IEC29500-1:2012] section 18.18.18) attribute that specifies the style of error alert used for this data validation.

imeMode: An ST_DataValidationImeMode ([ISO/IEC29500-1:2012] section 18.18.19) attribute that specifies the Input Method Editor (IME) mode enforced by this data validation.

operator: An ST_DataValidationOperator ([ISO/IEC29500-1:2012] section 18.18.20) attribute that specifies the relational operator used with this data validation. If type is "custom", "list", or "none", the value of the operator attribute is undefined and MUST be ignored.

allowBlank: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether the data validation treats empty or blank entries as valid.

Value	Meaning
"true"	The data validation treats empty or blank entries as valid.
"false"	The data validation treats empty or blank entries as invalid.

showDropDown: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether to display the drop-down combo box for a list type data validation.

Value of showDropDown	Value of type	Meaning
"true"	list	Displays the drop-down combo box.
"false"	list	Suppresses the drop-down combo box.

showInputMessage: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether to display the input prompt message.

Value	Meaning
"true"	Display the input prompt message.
"false"	Suppress the input prompt message.

showErrorMessage: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether to display the error alert message.

Value	Meaning
"true"	Display the error alert message.
"false"	Suppress the error alert message.

errorTitle: An ST_Xstring ([ISO/IEC29500-1:2012] section 22.9.2.19) attribute that specifies the text of the title bar of the error alert. The length of this string MUST be less than or equal to 32 characters.

error: An ST_Xstring attribute that specifies the message text of the error alert. The length of this string MUST be less than or equal to 225 characters.

promptTitle: An ST_Xstring attribute that specifies the text of the title bar of the input prompt. The length of this string MUST be less than or equal to 32 characters.

prompt: An ST_Xstring attribute that specifies the message text of the input prompt. This string MUST be less than or equal to 255 characters.

xr:uid: An ST_Guid ([ISO/IEC29500-1:2012] section 22.9.2.4) attribute that specifies a unique identifier associated with this data validation rule

The following W3C XML Schema ([XMLSCHEMA1] section 2.1) fragment specifies the contents of this complex type.

1270

1271

1272

1273

1274

1275

1276

1277

1278

1279

1280

1281

1282

1283

1284

1285

1286

1287

1288

1289

See section 5.4 for the full W3C XML Schema ([XMLSCHEMA1] section 2.1).