Breadcrumbs

Data Validation: Custom Validation: Validate data based on formulas or values in other cells

  1. Select the cell/cells where you want to apply the data validation.

  2. Select Data → Data Validation. You can also apply data validation to a group of cells, entire rows/columns etc.

    Capture 2019-08-28 at 19.51.13.png

  3. Data Validation dialog appears on the Right. Select the "Custom" criteria from the drop-down and enter the formula
    In this example, want the text to start with "ABC-", so we selected and cell A2 and used the formula "Countif(A2,"ABC-*")" to set the validation for A2. This validation now can be copied to other cells too.
    Capture 2019-08-28 at 19.55.30.png

  4. (Optional) Enter the input message for users
    A message that will be displayed when the user hovers over/selects the cell can be entered here.
    If you want to Ignore the blank cells from the data validation, toggle the switch "Ignore Blank Cells" to ON.
    Capture 2019-08-28 at 19.56.39.png

  5. (Optional) Enter Message at input of incorrect values.
    In the lower section of the Dialog, you can choose to display a message when the user enters invalid data. To display an alert, select the "Show Alert" box
    Different Alert types are "Error, Warning and Information". Select the required Alert type, title and the message you want to display.
    Capture 2019-08-28 at 19.57.30.png

  6. Click OK once the values are selected.

  7. Note: Any incorrect values will be circled red and hovering over/selecting the cells will show the input message.
    Capture 2019-08-28 at 19.58.44.png


  8. Note: Entering Incorrect value will show up the error message
    Capture 2019-08-28 at 19.59.39.png

See Also: How to use Data Validation feature