Create Multiple, Dependent Data Validation Lists

Locked lesson.

Upgrade

Quick reference

Create Multiple, Dependent Data Validation Lists

Use XLOOKUP to create multiple, dependent data validation drop-down lists. 

When to use

We use this technique whenever we want to create a drop-down list that relies on the selection made in a previous drop-down list. 

Instructions

In this example, we need to create a drop-down that lists the departments and then another dependent drop-down that lists the employees depending on what department has been selected in the first list. 

We also want everything to be dynamic so that if a staff member moves to a different department, the data validation lists update automatically. 

Create a Data Validation List - Departments

The first thing to do is create a data validation drop-down that lists the departments. 

  • Click in cell A7. 
  • From the Data tab, in the Data Tools group, click Data Validation. 
  • From the Settings tab, in the Allow section, choose List. 
  • Click in Source. 
  • Select the departments.

  • Click OK. 

Create a Dependent Data Validation List

Now we have our first list, we need to create a second list that depends on the previous selection. There are many different methods to achieve this but we are going to use XLOOKUP. 

  • Click in a blank cell anywhere in the worksheet. 
  • Type =XLOOKUP

The XLOOKUP function requires a [lookup_value]. The lookup value is the cell that contains our first data validation list.

The next argument is where we are looking for this information [lookup_array].

The final argument is where we are going to find our answer [return_array]. 

So, we are looking up the department 'IT' (cell A7), in the range E6:H6 and we are returning the result from cells E7:H19 as our answer could be anywhere depending on the selection. 

Notice, we have extended the cell range selection to accommodate any changes. 

The result of our formula is a list of the employees depending on which department has been selected in the drop-down. 

However, because each department has an inconsistent number of employees AND we extended our selection, we now have zero's in blank cells. 

We can use the UNIQUE function to remove these by choosing to show only items that appear exactly once. 

  • Click in the cell that contains the formula. 

We can add the UNIQUE formula with a TRUE argument to show only items that appear exactly once. 

This will remove the zero's from the list. 

We can now create our second data validation list. 

  • Click in cell B7. 
  • From the Data tab, in the Data Tools group, click Data Validation. 
  • From the Settings tab, in the Allow area, choose List. 
  • Click in Source. 
  • Select the first cell of the filtered list and add a # symbol. 

  • Click OK. 

Hints & tips

  • We use the # symbol when using dynamic array formulas. The # symbol selects the entire array. 
Login to download
  • 00:04 So far in this course, we've seen how we can use data validation drop
  • 00:08 down lists to select items from a drop down menu and
  • 00:12 have them highlight and do other things with our data.
  • 00:16 So in this lesson, I'd like to show you a couple of little tips as to how you can
  • 00:21 create dependent data validation drop down lists.
  • 00:24 So what exactly do I mean by that?
  • 00:27 Let's take a look at an example.
  • 00:29 Now if you take a look over here, I have in column A,
  • 00:32 it says Select Department, and in column B, it says Select Employee.
  • 00:37 And then I have a little table in the middle here that shows the different
  • 00:42 departments, and the employees who work in those departments.
  • 00:46 So what I want to be able to do is to create a drop down list underneath
  • 00:50 Select Department, which gives me a list of the four different departments,
  • 00:55 sales, marketing, HR, and IT.
  • 00:57 And then as soon as I select one of those, I want to have a secondary drop down list,
  • 01:02 which will show all of the employees within whichever department I've selected.
  • 01:08 So, this is dynamic data, and
  • 01:10 the second list depends on what I've selected in the first list.
  • 01:15 Now we could have more than this, you don't just have to have two lists,
  • 01:19 you could have three, our, five, so on and so forth.
  • 01:22 But the same procedure would apply to each of them.
  • 01:25 So let's deconstruct this and
  • 01:26 take a look at how we would go about putting this together.
  • 01:29 So we're going to start with the easiest thing first of all, and
  • 01:33 that is just creating a drop down list that shows the different departments.
  • 01:37 So we have those listed out just here, so this is a fairly straightforward task.
  • 01:42 We can simply click in cell A7, jump up to Data,
  • 01:46 go across to Data Validation.
  • 01:49 And we're going to create a list and the source for
  • 01:54 our list well that is this range just here, A6 to H6.
  • 02:00 Let's click on OK.
  • 02:01 And now I have exactly that.
  • 02:04 I can select sales, marketing HR or IT from this drop down list.
  • 02:09 Now the more complicated thing comes when we need to create
  • 02:13 the second drop down that effectively takes its cues from that first list.
  • 02:17 Now what I'm going to do, is I'm going to build this formula over in column K,
  • 02:21 just simply so you can see how this works.
  • 02:24 Now there are a few different methods that you can use to do this and
  • 02:27 everybody has their favorite method.
  • 02:29 Mine by far is to use XLOOKUP to do this.
  • 02:33 So we are going to start out with XLOOKUP.
  • 02:36 Now the first thing it's asked me for here is the lookup value.
  • 02:40 Now my lookup value is going to be whatever we've selected in that first drop
  • 02:45 down there.
  • 02:46 So the value in cell A7,.
  • 02:49 here are we looking up that value?
  • 02:52 Well, that value exists over here in this range.
  • 02:56 So I'm saying look up IT in this range just here, E6 to H6.
  • 03:02 What do I want to return?
  • 03:04 Well, this is basically where am I going to find my answer.
  • 03:07 Now, I can't simply just select the IT column because
  • 03:10 that's what I currently have in H7 because as soon as I change this drop down,
  • 03:15 it needs to be able to look in the other columns to find the correct names.
  • 03:19 So my return array, what I want to return could be anywhere in here
  • 03:24 depending on what I've selected in that first menu.
  • 03:28 Now I'm going to extend this a bit further down to accommodate for
  • 03:31 any changes to this list.
  • 03:33 Alternatively, you could put this into a table before you begin.
  • 03:37 Now those are the only arguments I'm going to complete for this.
  • 03:39 Let's close the bracket and hit Enter.
  • 03:42 And you can see it gives me that filtered list.
  • 03:44 So currently, it's looking for all of the people in the IT team.
  • 03:48 And I can see all of them listed just here.
  • 03:50 That is correct.
  • 03:52 If I were to change this drop down to let's say sales,
  • 03:56 I get a different list of people.
  • 03:58 And those are all of the people who work in the sales team.
  • 04:01 So this little XLOOKUP is working pretty well with this first drop down.
  • 04:07 Now can you notice one thing that might cause an issue?
  • 04:11 Take a look at the filtered list, we have all of these 0s at the bottom.
  • 04:15 Now that's because each of our lists are different lengths.
  • 04:19 So the marketing list is much longer than sales, HR, and IT.
  • 04:24 And because when I selected my range I added a few extra rows onto the bottom,
  • 04:29 this is why we're getting 0s in here.
  • 04:31 This is basically telling me that there's no data in that particular cell.
  • 04:35 Now you could leave this, it does look a little bit messy, so
  • 04:38 let me show you how you can get rid of this.
  • 04:40 We're going to go up to the first name in the filtered list, and
  • 04:43 we're going to edit our formula.
  • 04:46 And what we're going to do here is we're going to add onto the front the unique
  • 04:50 formula.
  • 04:51 As I mentioned towards the beginning of this course,
  • 04:54 UNIQUE is one of my most used functions.
  • 04:57 Now our array is going to be generated by the XLOOKUP.
  • 05:01 And what we're going to do is we're going to jump over the by call argument
  • 05:05 and go straight to exactly once because what we need in here to get rid of those
  • 05:10 0s is a true.
  • 05:11 It's only going to return items that appear exactly once.
  • 05:15 Let's close that bracket, hit enter, and you can see it gets
  • 05:20 rid of all of those 0s and we have a much neater-looking list.
  • 05:25 So now that we have our dynamic list working,
  • 05:29 all we need to do is add our data validation into column B.
  • 05:33 So I'm going to click in cell B7.
  • 05:35 Let's jump back up to Data, into Data Validation.
  • 05:41 We're going to select List, and our Source is going to be our filtered.
  • 05:47 I don't need to select the entire range,
  • 05:50 I can simply click on the first one and use the hash or pound symbol.
  • 05:55 Because UNIQUE is a spill array,
  • 05:58 that pound symbol basically means include everything below.
  • 06:02 So let's click on OK.
  • 06:04 And now we should find that we have all of those salespeople loaded up into here.
  • 06:10 If I change to marketing, my list is going to change and
  • 06:13 I can now select somebody from the marketing team.
  • 06:16 You might be thinking to yourself, well, that's all well and
  • 06:19 good, but how is this helpful?
  • 06:21 Well, it could be that you have some kind of chart attached to this, so
  • 06:24 maybe whenever you select a specific employee,
  • 06:27 the chart updates to only show that employee data.
  • 06:29 There could be numerous things that you have that rely on selections in a drop
  • 06:34 down to maybe output a formula or
  • 06:36 maybe change something on a chart, things like that.
  • 06:39 So knowing how to set up dependent data validation drop down this is a really
  • 06:44 useful skill.
  • 06:45 And of course, once you are finished with your filter list,
  • 06:48 if you do not want this to show you could put it onto a different worksheet or
  • 06:52 you can simply right-click and choose Hide.
  • 06:54 [BLANK_UDIO]

Lesson notes are only available for subscribers.

Gift this course