MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Complex Validation Using a Formula


October 03, 2019 - by Bill Jelen

Complex Validation Using a Formula. Photo Credit: Clément Falize at Unsplash.com

The method previously explained in Excel 2019: Dependent Validation using Dynamic Arrays is fine if you have Dynamic Arrays. But a lot of people running perpetual versions of Excel won't have Dynamic Arrays for years. Other published methods for Dependent Validation require a new named range for every possible choice in the first and second drop-down.

I was doing a seminar in Mobile, Alabama and several people there wanted to set up a three-level validation, but they did not care about having drop-downs to choose from. "I just want to validate that people are typing the correct values."


Rather than use the option to allow a list, you can set up custom validation using a formula. Say that you have a table with hundreds of valid selections.

A validation database with Country, State, and City.

If you think that your list will grow over time, format it as a Table using Ctrl+T.

Set up a named range for each of the three columns. This step is necessary so you can refer to each column and the names will grow as the table grows.

Select A2:A551. In the Name Box, type cCountry and press enter.

Make the whole validation database into a table using Ctrl+T. But then, select the countries in A2:A999 and type a name in the name box of CCountry.

Name B2:B551 as cState. Name C2:C551 as cCity.

Here is the area where you want people to type a Country, State, and City.

It is always easier to build and test your formulas for conditional formatting and validation in a cell first. Take a look at the formulas shown below to test each of the entries.

Instructions in the worksheet say to Fill Out This Form. There are three cells where people type Country, State, and City. The formula to make sur Country in F3 is correct is: =COUNTIF(cCountry,F3)>0. The formula to validate State in F4 is: =COUNTIFS(cCountry,F3,cState,F4)>0. The formula to validate City is =COUNTIFS(cCountry,F3,cState,F4,cCity,F5)>0. Each of these will return TRUE if the entry is valid.

Once those formulas are working, edit cell H3. Using the mouse, select the characters in the formula bar and press Ctrl+C to copy. Select F3 and press Alt+D L to open the Data Validation drop-down. In the Allow box, choose Custom. This will reveal a Formula box. Paste your formula in that box.

In Data Validation, open the Allow box and choose Custom. Type a formula of =COUNTIF(cCountry,F3)>0

Optionally, fill out an Input Message and Error Alert. Repeat to put the H4 formula as the validation for F4 and the H5 formula for validation for F5. The result: it will prevent a wrong entry.

When you select the City cell, a tooltip appears with: Type a City. The City must be valid for the selected state and country. When you type something wrong in the cell, an Error Alert says Invalid City: The City Is Not Valid For the Country and State.

Title Photo: Clément Falize at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.