The type of data that can be entered in a cell can be defined and/or formatted for end users. This functionality is called data validation. For example, you can show users "alias" input data for a cell, but then write actual (i.e., not formatted) data into the cell.
For example, the input cell in the screenshot below has been defined to allow a limited selection of value options that can be applied to the data:
Data validation is accessed from the Formulas menu:
The Data Validation dialog contains three tabs: Settings, Input message, and Error alert.In the Settings tab, you can specify criteria for data entry. The other tabs let you set up customized messages for input and errors. Note that these messages can be set up even if you do not set validation criteria.
Settings
To begin, set the "Allow" dropdown to List. You will then see the options for defining the data source.
Validation criteria
Ignore blank
When checked, no data validation will be carried out when nothing is entered into the cell.
Source
Values can be defined with a formula (which can include a range of cells), a Subset, or an ODBC query. You can also create a new Subset directly from the dialog by clicking the Subset button:
.
Please be aware of the Constraint for the data sources formula and ODBC query.
When using a Data validation list inside a DynaRange, the data validation source formula will not change cell references relative to its position in the DynaRange.
Note on copying data-validated cells: once you have defined data validation for one cell, you can copy it to another cell within the same sheet, but not from one sheet/workbook to another sheet/workbook. For cells in other sheets and workbooks, data validation must be defined again.
Show formatted value/alias
To show one set of values in the data validation dropdown list, but then set different values in the spreadsheet cell when the user selects a dropdown option, select the option Show formatted value/alias. Note the following restrictions:
- The Subset that defines the alias must be used as the source for the data validation, OR
- The source must be a range or formula that returns data conforming to the internal Subset structure. In this case, this data must consist of three columns: the first column represents the value to set in the cell; the second column represents the value to show in the Data Validation dropdown list; and the third column represents a numeric integer value, such as 0.
For example, a cell with the following data-validated values set:
would be defined by the following named range:
In this scenario, the Alias defined in the second column of the source range is shown to the user in the dropdown list and represents possible data entry options. The actual values that will be set in the spreadsheet cell once the user selects an option are generated by the splashing syntax (indicated in the first column) used to execute the selected writeback operation.
Write formatted value/alias
When this option is selected, the shown value that the user selects in the cell dropdown list is written to the cell.
Input message
In the Input Message tab you can specify whether an input message should be displayed, and if so, you can enter the message title and the message content.
Error alert
In the tab "Error Alert", you can specify whether an error message is displayed on incorrect input, and if so, set a stop, warning, or information message, each with title and content.
- The stop message allows only values defined in the list.
- The warning message requires the user to provide different input.
- The information message and the different input is accepted.
- If "Show error alert after invalid data is entered" isn't checked, then each cell entry is accepted without further message.
Updated December 9, 2024