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.

We need;

• PickedParentID
• PickedParentName
• PickedChildID
• PickedChildName

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

 =VLOOKUP(PickedParentID,ParentList[#Data],2,FALSE) 

And in the range ‘PIckedChildName’, add

 =VLOOKUP(PickedChildID,ChildList[#Data],3,FALSE) 

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

 =ParentList[Parent] 

‘OK’

Select ‘Define Name’ from the Formulas toolbar again, this time Name = ChildValues and Refers To =

  =OFFSET(ChildList[#Headers],MATCH(PickedParentName,ChildList[ParentID],0),2,COUNTIF(ChildList,PickedParentName),1) 

‘OK’

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.

Fileà Options

Customize Ribbon

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)

1 comment