I have to admit, this one took a little researching on my part. My boss stormed into my office with a quizzical look on her face (a usual occurrence) and said she was having difficulty doing something in Excel. She’s normally pretty good with Excel, so I know when that is her problem, it’s not going to be an easy fix. She proceeded to go to my white board in order to sketch out her problem (another indication that this was going to be a doosie) and then stumbled over her own words and drawings (the final nail in the coffin…this was going to be a fun one).
In a nutshell, her problem was this: she’s creating an interactive spreadsheet where the user selects an item from Dropdown A and then picks a different item from Dropdown B. Dropdown B is populated with items based on what was selected in Dropdown A. A simple example would be when you go to one of those websites that sells car parts or quotes car values, they first ask you for the make of the car, and then ask you for the model. The “model” dropdown only includes cars of the given make.
Shouldn’t be a problem, thought I. I’ll just write a macro that fires when the first dropdown is changed and populates the second dropdown accordingly. “And I can’t use a macro,” she said, “because it’s going to executives.” Ah, executives. The struggle to explain how to run a macro when Excel defaults to disabling them is an eternally frustrating one. And macros always tend to fail at the most embarrassing times.
So, what to do now. I knew that I could name a range, and I knew that I could use a named range for the data validation. What I did not know was this little gem of a function:
This function looks at the value of a cell and considers the text of it, not the reference to it. I sincerely doubt that I’m explaining that correctly. Essentially, we are going to use the value of the first drop down to define the named range that is to be used in the second drop down. Simple.
These are the lists we will be using for this example. The first column is car manufacturers (makes) and the second list is models. Note that the data does not define which models are made by which manufacturers – that is going to be defined by named ranges.
First, split up the models column into the named ranges (makes) the models represent. For instance, cells I2:I5 are Fords, and cells I6:I8 are Toyotas, so the named ranges should be “Ford” and “Toyota”.
Next, use the first list of makes as a List Data Validation for your first dropdown. Do this in cell A3.
Finally, and here comes the magic, use an INDIRECT formula for the List Data Validation of the second dropdown. This will use the first dropdown’s value as the name of the Named Range used to populate the second dropdown.
That’s it! When a user selects an item from the first dropdown, let’s say “Ford”, the second dropdown is populated with items from the named range called “Ford”. Note that List Data Validation needs to be a one dimensional array. If you require a second dimension to your data, consider a VLOOKUP to be used in conjunction with the above method.
As always, I hope I was explicit enough with my post, but if you have any questions or problems with this method, please do not hesitate to post comments below.