< Go Back

Data validation exists in list

HOW TO CHECK IF DATA VALIDATION EXISTS IN LIST

Just as it is with using Excel to solve many things, you can also use it to check if Data validation exists in list. This post will guide you on how to check if Data validation exists in list formula approach in Excel.

The general formula on how to check if Data validation exists in list:

=COUNTIF(list,cell)>0

Explanation

To show that an item from a list exists in a cell, you can employ data validation rule with a formula based on the COUNTIF function. This custom validation formula counts occurrences of the specified brands in the list. Any count above zero will pass validation.

How the COUNTIF function works

The data validation rules are activated once a user alters or add a new cell value. In such instance, the COUNTIF function returns TRUE when a value exists within a specific range or list and validation become successful. If on the contrary, the expression returns FALSE and the data validation fails.

Example

Figure 1. Data validation exists in list

In figure 1 above, the data validation that is applied to D4:D9 is:

=COUNTIF(BRANDS, D4)>0

Once a user alters the cell value, the function will be activated.

Useful notes

  • Excel consists of many built-in data validation rules for dates.
  • With this function, you can develop tailored validation rules for any column lists when you do not want the drop-down actions.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar