In this tutorial we’ll see how we can use Conditional Formatting, using the ISBLANK, COUNTIF, and INDIRECT functions, to verify the accuracy of selections from a Data Validation drop down list in Excel.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a small chart that has two columns: Continent and Country. I’ve created Data Validation drop down lists so I can choose one of three continents in column A and one of 49 countries in column B:
Notice, if I choose a country that is within the selected continent, it formats as green, but if incorrect, it formats as yellow.
I’ve created 5 named ranges – one for each continent, one to list all the countries, and one to list the three continents:
Here are the lists I’ve used to create those named ranges:
In Conditional Formatting, I’ve used three different formulas – all used to format $B$2:$B$4:
The first is formatted as normal
The second is formatted with a green fill
The third is formatted with a yellow fill
Here is the setup in Conditional Formatting for these three formulas:
The key is the use of COUNTIF and INDIRECT in the formulas. Here is how it works:
Since I’ve named the range of each list of countries within a continent, the choice of a continent in column A is actually the name of that range. (Notice that I had to use the underscore instead of a space in the continents’ names – you can’t use spaces when creating named cells or ranges). The INDIRECT function “Returns the reference specified by a text string”. So, “INDIRECT(A2)” takes the text string, which is the name of the continent selected in column A and converts it into the range that is the named range that uses that continents name. That becomes the range for the COUNTIF function. So, if the count of that country in that range is 1, then the second Conditional Formatting rule is true, and it will fill it green. If the count is 0, or <>1, then it will fill it yellow since it does not appear on the list.
Watch the video if you need a more step-by-step explanation of the process.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!