QUESTION:
“Is it possible to make one validation column dependent upon choices from another validation column?”
Here’s an example;
Imagine you have 2 columns in an Excel list, one column for the Make of car entries and another column for the Model of the car entry. If a user selects “Ford” as the make, when a model is selected they should only be able to pick from “Ford Models” (Mustang, Escort, Focus). If “Chevy” is chosen, then they should only be able to pick from “Chevy Models”.
ANSWER:
This is definitely possible and can be accomplish is a number of ways. My first thought was to write some VBA code that would identify which Make was selected and then populate the appropriate choices through the code. Then I thought this would be a place to create an Excel VBA UserForm to control the users experience with specific controls to interface with in order to do the data entry.
Both the above would work great, but takes some knowledge of VBA and maybe not entirely necessary. I finally decided on using Excel’s Data Validation feature, combined with Name Ranges and Excel’s INDIRECT Function.
Here is how it goes…
Setup three columns, somewhere off to the side of the main list (maybe even on another worksheet).
MAKES Column
FORD Column
CHEVY Column
Toyota Column
Use the NAME RANGE feature to name each of these 4 columns
E2:E4 = “Makes”
F2:F4 = “Ford”
G2:G4 = “Chevy”
H2:H4 = “Toyota”
Do this for each of the four columns.
Setup the Data Validation lists
Select the “Make” column (A2:A…)
Set the Data Validation to pull the lists of Makes
Select the “Model” Column (B2:B…)
Set the Data Validation to the INDIRECT() Function
=INDIRECT(A2)
The INDIRECT() Function will take the value of whatever entry is chosen in A2 (Ford, Chevy, Toyota) and turn it into a range reference. In this case, the Name Ranges you created earlier.
When you click OK you may get a warning, just click YES.
There you have it. Click into the Make column, select a Make. Now click the Model column and you will get the appropriate Models to chose from.
Hope you’ve enjoyed this. For the working file, download here: ExcelDynamicValidation-01