In this article, we will learn about how to create cascading drop down list using conditional formatting. To create the cascading drop down list we will use Countif and Indirect functions.
Let us understand with an example:
We have a list of Fruits & Vegetables.
Now, we want to create cascading drop down list which will highlight the incorrect selection (highlight the value if it does not belong to Fruits or Vegetables)
We need to follow these steps:
We will use Define Name to create named ranges for Vegetables & Fruits
The next step is to create data validation list for Fruits & Vegetables say in range A13:A18. Refer below shown screenshot
In range B13:B18, create data validation list with formula referring to cell A13 using Indirect function.
Copy the cell B13 & use Paste Special & select Validation in range B14:B18
This will copy the validation in the selected cells.
After the data validation step is over, now we can use the formula that will highlight the wrong selection.
The following snapshot shows everything fine.
But as soon as we change the Fruits to Vegetables in cell A13, you will find the conditional formatting takes over & highlight the incorrect selection.
Similarly, if we change value in any drop down in below range say cell A18 then it will be highlighted.
In this way, we can track the wrong selection made in drop down list & change it to correct value.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.