Excel "smart list": drop-down list dependent upon user's choice of another drop-down list

Almir's picture

When data entry is strictly defined, it is nice to have data validation list in place, so entries are uniform. Furthermore, a nice way to make data entry faster is to have "smart lists", or dependant lists. What does this mean?

When you choose an option from drop-down list in a column, you need only possible options listed in another column, based on the first choice. Let's look at a simple example with dogs and cats.

Example file is attached.

You need to fill in column A with animal type ("Dog" or "Cat"), and you can pick it from a drow-down list created by Data Validation. So far, so good, right?

In column B you need to enter Dog or Cat breed, respectively. In a separate sheet you have a list of breeds (Labrador, Terrier, Shepherd, Pit-bull for Dogs, and Siam, Thai and Other for Cats). You can pick it from a drop-down list as well. Now, to the tricky part...

When you select animal type in column A, you need drop-down list in column B (Breed) to dynamically change, in a way that it displays only breeds of the selected animal. In short: when you select "Cat" in column A, you don't want Pit-bull as an option in column B.

To achieve this you need 3 separate lists:

1. List of animal types (Dogs, Cats)
2. List of dog breeds (Shepherd, Terrier, Pit-bull, Labrador)
3. List of cat breeds (Siam, Thai, Other)

I suggest to convert these lists to 3 Data Tables (CTRL+T). Thus, when you add or change cell values, drop-down lists will be automatically updated.

Create named ranges: "Animals", "Dogs" and "Cats", each containing respective list from 1 to 3 (do not include column names).

4. Using Data Validation, in column A1 create drop-down list based on named range (Animal).
5. Using Data Validation, in cell B2 create drop-down list, List: =INDIRECT(A2)

What happens? When you select animal type in column A to "Dog", INDIRECT function in column B interprets it as that column B should offer only the list of dog breeds, and not the cats.

Change animal type to "Cat", to see dependant drop-down list in column B changes to list of cat breeds.

Dependent drop-down lists
AttachmentSize
SmartList.xlsx12.84 KB