When using dynamic Data Validation, there are points when the item shown does not match the category listed. In this tutorial, I’ll show you how to write a formula to advise the user that another item needs to be chosen. We’ll use four functions to accomplish this: IF, OFFSET, MATCH and COUNTIF.
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 list of categories, then next to that, each category in the header with the items for that category below:
I’ve named each of lists below based on the header names:
I’ve also created two data validation drop down lists, one for the categories and one for the items within those categories. Here are those lists and the formulas used to create that dynamic reference:
When I select a category, the list of items changes to match those in that category:
Notice, however, when I choose a new category, before I choose an item that belongs in that category, it will display an item that does not exist for that category. Here’s an example:
Here I had Fruit selected as a category and Oranges as the item. Then I changed the category to Candy, but hadn’t selected the item yet. If the user left it in this manner, they’d think that Oranges is an item in the Candy category. In the cell next to it, I entered a formula to tell the user that it’s not OK and to choose a correct item. The formula is:
Here are the functions that I used, along with their syntax:
Here is what each of the functions is doing:
I also wrote two Conditional Formatting rules so that OK will be in bold green and Change Item is in bold red:
I hope you’ll take some time and watch the video for the best explanation of how this formula works. I think you’ll find it useful.
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!