Data Validation is great when you want to limit possible entries to a predefined list. Sometimes, however, you want that list to be different based on another cell. This posts shows you how to use Data Validation to condition one list based on the selection in another.
To do this, we need to set up some lists and name then using Insert>Name>Define. Here are some lists that I set up to illustrate.
The comments at the bottom of the lists show what I’ve named them. The first name “Pubs” is unimportant. But the other two names match the entries in Pubs exactly. This is important as we’ll soon see. I’ve set up DV in cell E1 as shown in the comment to that cell. In E1, the user has the choice of Books or Magazines. Cell F1 also contains DV, but the choices in that list will depend on what is in E1. Here’s what the F1 lists look like for different E1 values.
The DV in F1 is setup like this:
By naming the dependent ranges the same as the cells in Pubs, the INDIRECT function can be used to refer to them.
One cautionary note: If you change E1 after F1 has been selected, you’ll end up with a value in F1 that’s not in the appropriate list. No warning, no error, nothing. To alert users of this potential problem, consider adding conditional formatting to F1 to make it standout if the entry is not proper. Here’s one way you can do it.