Easy Dynamic Dependent Data Validation Two Ways

before we had the luxury of dynamic array functions creating dependent data validation lists required using multiple tables and named ranges which meant a lot of setup however with dynamic array functions creating dynamic dependent data validation list is much quicker in this video i'll show you both the old way for those who don't have microsoft 365 and the new easier way for those who have dynamic arrays a dependent data validation list is one where the first selection dictates the items displayed in the second list so for example i can choose australia here and the list of regions i get are australian states let's take a look at how we set this up now before dynamic array functions we use named ranges and table names for example here you can see my table containing the countries is called country and if we have a look in the name manager under formulas you can see that i've named this table country list if we select the refers to you can see the marching ants around the country table now strictly speaking because the country table is on the same sheet as my data validation list is going to be i don't technically need to also give this table a named range but if your tables are on different sheets to your data validation lists as they often are then you need to give the table a defined name so that your list automatically includes any new countries you add to the table so let's set up this first data validation list selecting the cells on the data tab i want to go data validation here i want list and the source is going to be the named range i set up i'll press f3 to bring up the list of names and i can just double click to select it from the list there's only one there so it's obvious which one it is let's click ok there and now i've got my list of countries next i set up a table for each country's regions so you can see here the table names are usa australia and uk now it's essential that the names of these tables match the names of the countries and you'll see why next so to set up dependent data validation i'm going to select the cells first and then on the data tab go back to data validation here i want list and in the source i'm going to use the indirect function to return the country selected in column b now i don't want the second dollar sign for absolute referencing because i want this to be relative to the row that the data validation list is on so click ok let's select a country or region and then we get our list of regions that are specific to the country or region selected in column b likewise if i choose australia now i get australian states and if i choose usa i get american states now this technique works in all versions of excel but the next technique which i think is easier to set up requires a microsoft 365 license let's take a look in this example i'm using the same data but this time i can store it in a single table which is a more common layout this table is called tbl regions and i don't need to set up an additional name for it so the first thing i want to do is return a list of the country names and i can use the unique function to do that so referencing the country column i'll return a list of names now i want it sorted so let's wrap it in sort and instead of it spilling down the rows i want it to spill across the columns so i'm going to use transpose and there's my list of country names and if i add any more countries to this table this formula here will automatically add them to the end so with that set up i can insert my data validation list so on the data tab data validation then here i want list and the source is simply the first cell in my formula and on the end i want to add the hash sign this is going to tell the data validation list to pick up all of the countries in that spilled array so if a new one gets added and it ends up in cell k4 it's automatically going to be picked up in this range click ok and now i can choose a region from the list now i can set up the lists for the regions and for this i can use the filter function to get a list of regions for the countries listed in row four so filter the regions where the country equals the country selected here close parenthesis on filter now i'd like that list sorted so let's wrap it in sort and press enter so there's my list of australian states let's copy the formula across and now we have the lists for each regional country now you might be wondering why i didn't reference this build range in the filter function and that's because the filter function can't spill both down the rows and across the columns in this scenario so now i'm ready to set up the data validation for the regions let's select the cells insert data validation we want a list and i can use the x lookup function to return the list for the country selected in column b so x look up what are we looking up this country here i want to remove the absolute reference from the row i only want it on the column where am i looking at up in this row here what am i returning this row here now i'm not selecting all of the rows just the first one and then i'm going to close parentheses on lookup or x lookup and then we're going to add the hash sign what that's going to do is return the whole list and because they're all different lengths it's going to automatically adjust accordingly click ok and there i have my list for the uk let's select that's australia and usa so you can see how it works now you might be wondering why i just didn't take the filter function here and put it straight into my data validation source here and that's because filter returns an array and date validation lists only accept a range which is what x lookup is returning now if both of these techniques i can add new regions to the list so for example let's add usa and let's add in il so you can see it's now in this list here and if i look at my data validation list you can see it's there too i hope you found these techniques useful you can download the file for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching [Music] you

Channel: MyOnlineTrainingHub

Views: 14,019

Rating: 4.9820828 out of 5

Keywords: excel data validation, dependent data validation, drop down lists, dependent drop down lists

Id: pjLAnpBM9dk

Length: 7min 20sec (440 seconds)

Published: Thu Aug 26 2021