MS Excel - Dependent Dropdown List

in this video we will be looking for dependent drop-down lists now there's some prerequisite for this dependent drop-down list you should have some knowledge about the name range how do we create it how do you delete and edit the name range let's see that what is dependent drop-down list is if you have seen too many websites where you book a flight or a train or a bus you might see there are two drop-down list one list will tell you the names of the countries in that list you can select all the countries the other list will change according to the country name so same way we are going to design a list here so what would be the list is that's one list will be four countries like I have the country name as India Malaysia China and Canada just randomly have written similarly if I select some India like country India I should get names of list of cities in that country if you select Malaysia I should get names of cities in that country so how we go for it so that is simple but the prerequisite is name range as I said so how do we create a named range the very first step is this area that is the cell number d5 to d9 should have a name as India similarly e five to e8 e five - eh should have the name as Malaysia similarly this three have the name as China and this three should have the name as Canada so there are various method but the shortcut method to give a name to this entire area is you can see here first is I'll select this area press control key and then without leaving the control key I'll select the second column third column fourth column data this way I have selected a data now what exactly my requirement is all this data should get the name as this column heading that means this five should get India this three should get Malaysia and China and Canada so how do we put names to this place so I can go to the formulas tab and you can see there's a option known as create name for selection so I'll just keep my cursor here and if you see the shortcut is ctrl shift F 3 so even if I use control shift F 3 button the names will be given so now I'll just click on this option and if you see it has given as create names for the values for top row that means all the data will give get the top row name so I'll just click on okay so once I click on ok the work is done so I can click outside now exactly what happened I will tell you in the name box if you see all the names of 44 countries are appeared here so now if you click on India you see all the five names are selected if you click on China all the three names would be selected so what I am trying to say is this cities are getting the name of the countries so now let's proceed next step the step is I should get a list of all the countries at this please so getting the names of all the countries is very easy how we can go for it I'll go to data tab and this is the data tab and I'll select here data validation in this data validation I'll create a list that is names of all the countries list that is names of all the countries and I will click on this button I will select this yeah selected all the countries and I will click on ok so that's it so if I click on this drop-down this is very simple which you have seen how to create a drop-down list that is very simple here but now the main portion comes here what is the proportion is the cities should change according to the country name now how we can go for it follow the steps here so you can go to data validation select here the option as list from this list from this list I will type something here a function known as indirect now what is that indirect I'll type here equal to indirect and now I will select this cell number then I'll close this bracket so what is this indirect is it says this 3 D 13 is not a name but it is a named range it is not a value but it is the name range that means indirectly if it is right now China it will keep on calling the list of China that is a list for China so once I click on okay let's see whether it is applied or not so you see Beijing gone Zhou and Shanghai these three names are appeared so now if I select here my country India like this and if you see in the drop-down list I get all the five names that is Hyderabad Bangalore Mumbai Chennai and Delhi again I'll try for one more just click on this Malaysia and I'll click on this drop down I am getting Kuala Lumpur Johan Bo and some other countries some other city similarly what exactly happened was indirect is a function which will do which will call the name range behind that name range there are multiple cells which are assigned so this way you can create multiple drop-down list so I hope you understood the topic that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 522,521

Rating: 4.8584862 out of 5

Keywords: MS Excel, Excel, Microsoft, MS, MS Excel - Dependent Dropdown List, Dependent Dropdown List

Id: Onb-AqDbkcM

Length: 4min 49sec (289 seconds)

Published: Mon Jan 15 2018