How to Create Dependent Drop Down List In Excel
How to Create Dependent Drop Down List In Excel, Let's start that how this dependent drop down list is created, suppose we have table like this, in first column we have state names, we want to create drop down list of these states names. it is good first remove space between the names, if we have space between the states names this can effect the results of the dependent drop list, so this space we can remove in this way, first press control A, as sheet data is selected, press control F, in find tab press space button, and then press replace tab and write there under score sign or you can write any sign just to fill the space, under score you can write by pressing shift plus minus sign button, then will get this under score sign. after writing this press replace all button then will get this under score sign between all values where we have space.
after this we will select first states column, and from top task bar will select formula tab, from there we will select create from selection option, after selecting this automatically we will get short cut name of this selected area, top first value will be the name of this selection, as top name is states so this will be name of down area values, this you can see from here just left side of formula tab, as states short cut is selected it is showing the name of selected area, in same way we will create short cut tabs for all cities, top state name will be their head or short cut name and down there will be city names of that state, so in this way we can create shot cut tabs for all states, here you can see short cut tab is showing the names of all states, when i am selecting any state it is showing the selected area down that tab. so in this way when i am selecting any state name that tab is showing me selected area, that these cities are under that tab, i have created two work sheets, for your easy understanding, same work you can do on single page if you need, but for your understanding i have placed data or source information on 1 sheet and others on next sheet, here we will apply our function or dependent drop down list, as we need this drop down box under state names, so here we will place our cursor and from top task bar we will select data, under data option will select data validation from here, after pressing data validation option, we will get data validation form from that form we will press drop down box, and will select list option, as list option is selected that form will show us source option there we will write equal sign and after this we will write states that we need list or drop down box of states, after writing this we will press ok button, as ok button is pressed we will get drop down list of states, so our first drop down list is ready, now you can see there is tab as this tab button is pressed there are names of all states so from this list we can select any state names, and in next column we can create dependent drop down list of cities, that as any state name is selected we will get the name of that state cities. We will repeat the same work as last time we did, in next cities column in C5 cell we will place our cursor and we will select data validation option from data tab, and we will apply drop down list as applied in last column, as data validation form appears on our sheet from that form we will select list option, and in source tab will write equal sign after writing equal sign, we will write indirect, after writing this we will place open parentheses sign, we can get this sign by pressing shift plus 9 button, after writing this we will select B5, last column where already we have applied drop down list, as the name from that list is selected, here we will get the names of the cities of that state, as b5 is selected function will place dollars sign automatically along with that cell reference number, after this close the parentheses, by pressing shift plus zero keyboard button, after this press ok button, now our dependent drop down list is ready,
Excel sheet link: https://docs.google.com/spreadsheets/...
Scrol bar : • SCROLL BAR IN EXCEL [ HOW TO CREATE OR AD...
Mail Merge: • MAIL MERGE IN EXCEL TRICKY TOOL TO CREATE ...
IFRS 1 : • IFRS 1 [ International Financial Reportin...
IAS 2 : • IAS 2 Inventories - IFRS [ International A...
YouTube :-
/ @setlineexcel
Instagram :-
https://www.instagram.com/tv/B05VXAUn...
Facebook :-
/ excel-lovers-111636520186727
modal=admin_todo_tour
LinkedIn:
/ set-line-537477191
Mix: https://mix.com/setlineexcel
Pinterest: / setlineexcel
https://www.tumblr.com/blog/setlineexcel
https://twitter.com › ExcelSet
Blogger: https://setlineexcel.blogspot.com/p/e...
________________________________
PLEASE WATCH LIKE COMMENT AND SUBSCRIBE MY CHANNEL Setlineexcel FOR MORE USEFUL AND NICE VIDEOS.