00:00do you want to learn how to create a dependent drop-down list that works something like this
00:04let's say for example if you select Canada from the country only Canadian cities are available
00:09for selection likewise if you select Japan only Japanese cities are available for selection
00:16let's learn the first thing you're going to have to do is to assign a name for each list place
00:20your
00:21cursor on the data set where you have countries and cities and press ctrl a and this will select
00:26all your data set but what we want to select is only the cell with text in it to do
00:32that you're
00:32going to press ctrl g which is going to open a go to pop-up window click on special and
00:37select constant
00:38and click on ok and you can see only non-empty cells are selected now to assign each list the
00:45name as a
00:47the header itself you're going to go to formula and under define name you're going to select create
00:52from selection and on this pop-up here make sure you leave the top rows only like this and click
00:59on
00:59ok at this point if you look at your name box your list name is already in there next thing
01:05you have
01:06to do is to create a drop-down list for the country select the cells or rather the column of
01:11cells where
01:12there's country and then after that you go to data and under data tools you're going to go data validation
01:19and this pop-up will appear here make sure you're on a settings tab under allow you're going to select
01:25as list and in this source you're going to click on this source to select it and now here you're
01:30going
01:30to press f3 and this will open up the list of names that's available on your workbook i have a
01:37few names
01:37that have defined so far but the one i'm interested will be the country because this is what i want
01:42to
01:42populate with the canada brazil japan australia and germany when you do that click on ok and ok
01:48one more time as you can see now my drop down on this column of countries will have all the
01:54countries
01:55available like this next to make the city a dependent drop-down list to the country you're
02:00going to select the column of city like this and then after that go to data data tools data validation
02:07and on this data validation again make sure that you're on the setting tab on the allow you're
02:12going to select as list and on the source you're going to say equal indirect and then open parenthesis
02:20and you're going to select the adjacent cell to this very first cell here and then over here you're
02:25going to press f4 twice like this so that your column is fixed and your row is variable and then
02:32close
02:32parenthesis and click on ok and then you're done you can do a quick test you can see only canadian
02:39cities are available here because i've selected a country as canada and let's say if i select brazil
02:44here only brazilian cities are available and of course you can extend this list to put more brazilian
02:50city or more canadian or japanese or german city and this list will get updated accordingly as well
Comments