Reference: Modeled Sheet Validation Rules
When you create modeled sheet validation rules, you use row dot notation Boolean expressions and column codes. You can add extra lines and spacing for better readability. You can also use # to include comments.
If you enter invalid expressions, we display error messages that might help you fix the issues.
You must manually ensure that you use columns with the correct data type in your expressions. We support these functions with these data types:
Function | Supported Data Types |
|---|---|
day | Date |
div | Number or check box |
if | Logical |
isblank | All data |
length | String |
month | Date |
quarter | Date |
substring | String |
switch | Date, number, or string |
toDate | Date |
toNumber | String |
year | Date |
Validation Rule Examples
Rule | Expression | Type |
|---|---|---|
Label must be more than 4 characters. | length(ROW.Label) > 4 | True |
Requires that the Selling Price has a value greater than or equal to the Purchase Price unless Pricing Exception has a value. | if (isBlank(ROW.pricing_exception), ROW.purchase_price, ROW.selling_price) >= ROW.purchase_price | True |
End date must be greater than or equal to the Start Date, but only if End Date is populated. | if (not(isblank(ROW.end_date)),ROW.end_date, ROW.start_date) >= ROW.start_date | True |
Start date must be less than December 21, 2020. | ROW.StartDate < todate (2020, 12, 21) | True |
Start date must be equal or greater than CY 2023. | year(ROW.StartDate) >= 2023 | True |
Requires that all populated values in the Label column start with the required prefix "required-prefix". | if (isblank(ROW.Label),"required-prefix",substring(ROW.Label, 1, 15)) = "required-prefix" | True |
Requires Hiring Exception to be populated if Hire Date is in the 3rd or 4th quarter. | not (isblank(if (quarter(ROW.hire_date) >= 3,ROW.hiring_exception,"exception_not_required"))) | True |
If Change Request is selected, Comment cannot be blank. | ROW.ChgRqst=0 or (ROW.ChgRqst = 1 and not(isblank (ROW.comment))) | True |
If Level is QA, employee must have an annual salary. | ROW.LEVEL!="qa" or (ROW.LEVEL = "qa" and ROW.per = "yr") | True |
If not blank, # of Employee must be greater than 1. | isblank(ROW.numEmployee) or (not(isblank(ROW.numEmployee)) and ROW.numEmployee > 1) | True |
TAM must be greater Revenue. | ROW.tam > ROW.Rev | True |
Hours Per Week must be between 20 and 40. | ROW.HoursPerWeek >= 20 and ROW.HoursPerWeek <= 40 | True |
If Approved, Fieldmgrscore must be greater than 1. | Row.Approval != "yes" or (ROW.Approval = "yes" and ROW.fieldMgrScore >=1) | True |
All employee IDs (text column) must start with a numeric value under 200. | tonumber (ROW.EmployeeID) <200 | True |
A comment is required if Pay increased by 10%. | (div(ROW.Input_Total_Base_Pay - ROW.Total_Base_Pay, ROW.Total_Base_Pay) <0.1) or not isblank(Row.Memo) | True |
If expense account is populated, then Name of Requestor and Cost Center Owner must also be populated. | isblank(ROW.ExpenseAccount) or not (isblank(ROW.ExpenseAccount) or isblank(ROW.NameOfRequestor) or isblank(ROW.CostCenterOwner)) | True |
Asset class value must be associated with IT products. | switch (ROW.AssetClass, "hardware", "it product", "software", "it product", "non it product") = "it product" | True |
When I plan for a new executive hire, a comment is mandatory. | ROW.Position_Status="open" and ROW.Supervisory_Organization = "supervisory_executive_management_group" and isblank(ROW.Memo) | False |
TAM cannot be blank. | isblank(ROW.tam) | False |