Excel- Activate a validation list based on specific criteria

November 2017



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
Published by deri58. Latest update on February 15, 2013 at 04:05 PM by aquarelle.
This document, titled "Excel- Activate a validation list based on specific criteria," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).