Excel- Activate a validation list based on specific criteria

December 2016



It is possible to enable or disable a validation list based on specific criteria (numeric value or text in a cell or range).
If the target values are on a separate sheet than the one containing a list of validation, it is advisable to name the range containing the target data (this will help to shorten the formulas).

In our example data can be found on the sheet named "Sheet2". Highlight the concerned range (Sheet2 A1: A20) > (Name / Define Name), name as "Titi" for example.
Before creating the validation list it is imperative that the condition criterion is set to "TRUE".
If for example the activation of the validation list defined by the the contents of a cell (cell B5), enter X value in this cell/
In cell D3 we want to create the validation list that displays the data in the named range Titi. Usually we would have entered as validation formula like:
=IF(B5<>"",Titi,"") 

It is now possible to test the validation list when cell B5 is non blank.
It is possible to think differently by integrating a test in the definition of another named range called "Liste" to avoid interference with the named range "Titi"
=IF(Feuil1!$B$5<>"",OFFSET(Feuil2!$A$1,1,,COUNTA(Feuil2!$A:$A)-1),"") 

Accessibility of a validation list can be subjected to any kind of control: Suppose we wanted to use as validation list as the sum of the values ??in the range A1: A20 (number less than or equal to 20).
In this case the condition is true when the values less than or equal to 20, including empty cells that return zero.
The formula for the creation of the validation list will
=IF(SUM($A$1:$A$20)<=20,Titi,"") 

In the case the validation list requires a minimum amount such as 20 and above, it is mandatory to enter in the range A1 to A20, a value that is equal to or greater than 20.
Then create the validation list of this type:
=IF(SUM($A$1:$A$20)>=20,Titi,"") 

Download the sample file: http://cjoint.com/12nv/BKzoUyiDQdq.htm

Related :

This document entitled « Excel- Activate a validation list based on specific criteria » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.