Populating one Drop Down based on the Selection of Another in Excel – Without VBA
This post will show you how to populate a drop-down menu in Excel with values, based on the value selected in another drop-down menu – all without touching VBA
There are plenty of ways to achieve this is VBA, but I wanted a non-VBA solution. There are also some sleek DAX approaches we could use, but again, I’m after a more cross-compatible approach – but DAX is certainly something I plan to cover off soon!
There is a fair bit of prep work involved – but once we’ve done the prep work, it will come together very quickly.
Firstly, put your data into a grid, with your ‘Parent’ name, and an ID value (number 1 to x) to the left of this.
To make our life easier, we’re going to convert this grid into a TABLE.
Select your grid
Go to the INSERT tab, and select TABLE
Ensure ‘My table has headers’ is selected, and press ‘OK’
When you create a table, it’s given a name, but we want to change it to something more meaningful.
Select your table, and a DESIGN tab will appear. Go to this tab
On the far left is the Table Name box. Enter a different name – I’ve used ‘ParentList’ (we will reference this name later, so advise sticking to ParentList for now!)
Now, create another grid – this one should contain an ID field (1 – x), the Parent Name, and the corresponding Child Name.
As before, convert this to a Table, and name the table ChildList
Note: It’s very important that your data is sorted on the ParentID field!
Next, we need to setup 4 single-cell Named Ranges
To do this, simply select a cell, and to the left of the formula bar (where is shows the cell reference), overtype the cell reference.
I find it useful the add a column next to these values, and enter the name of the Named Range, so I know what each value is showing me
In the range ‘PickedParentName’, add the formula
And in the range ‘PIckedChildName’, add
We do have a couple more named ranges we need – but need to create these manually.
On the FORMULAS tab, select ‘Define Name’
In the Name box, enter ParentListValues, and in the Refers To box, enter the formula
Select ‘Define Name’ from the Formulas toolbar again, this time Name = ChildValues and Refers To =
Ok, we’re nearly there!
Time to add the drop-down boxes. Firstly, ensure you have the Developer Tab available – if not, you can enable it.
On the right, there is a list of the available tabs. Locate Developer, and Tick it.
‘OK’ out of that screen
Go to the Developer tab.
Select ‘INSERT’, and from the ‘Form Controls’ list, select ‘Combo Box’
IMPORTANT: Do not select Combo Box from the Active-X Controls list
Click on your spreadsheet, and this will add a combo-box (Drop Down).
If you just get a large square with an arrow, just resize the box to make it more rectangular, and it will sort itself out.
Right-Click on this drop-down, and select ‘Format Control’
In the ‘Input Range’ box, enter ParentListValues
In the ‘Cell Link’ box, enter PickedParentID
Follow the same process to add another Combo-Box, go to Format Control, and this time
Input Range = ChildValues
Cell Link = PickedChildID
And that should be it!
Select your Parent item from the first drop down, and the second drop down should then populate with the relevant child items. Selecting a child item will result in the cell ‘PickedChildName’ being the child item you selected.
So how does it all work?
Hopefully, most of it was fairly self-explanatory. Really, most of what we did was just giving everything nice neat names to make life a bit easier.
The clever part was the range we created as the Input Range for the second Drop Down – ChildValues
The formula we entered uses the Offset & Match functions to find the PickedParentName in the list of ParentID‘s. By then assigning a value to the optional ‘Height’ parameter of the Offset Function, Excel creates a range starting from the first value, extending to the last matching value.
By then assigning this named range to the drop-down, it picks up the dynamic range of values to populated. (Hence why it’s important to Sort your values in the Child Vaues table)