Excel 2024: Complex Validation Using a Formula

The method above 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.

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.

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.

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.

---

---

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.