How To Create Dynamic & Dependent Drop Down Lists In Excel [Free Download + Training]

hello this is Randy with Excel for freelancers  and in today's video we're gonna show you how to   do dynamic drop-down lists that automatically  change based on the selection so for example   if we were to choose electrical our  category will change automatically and   if we were to do let's say plumbing our  categories and subcategories would also   change we are also going to show you how to  add automatically items that are not in the   list dynamically as well so we really look  forward to showing that to you how we can   add those automatically to the list just  like that okay thank you let's get started alrighty let's get started here and the basic  premise of this training you're going to learn   how to create multiple lists dynamically as well  as types categories and subcategories or whatever   you want to call them and basically when we have  a new item we have specific types right and right   now we've got four different types but let's say  we want to add one flooring okay we can add one   and it's gonna tell us this is not in the list  do we want to add it in boom just clicking yes   and all of a sudden it's in the list just like  that okay we can do the same thing with category   we don't have any categories because we just  created this type so maybe we want ceramic tile   and that'll give us the same option where we want  to create it and then we want to quick brown tile   let's just say that and now we have it and now  we've got those now we've gotten flooring all   of those details and we can create additional ones  we have ceramic tile here but let's say we want to   create carpeting just like that and we're creating  it and also since there's nothing in the sub list   we can create let's say wool carpeting and just  like that it's added so we're going to show you   how to add these things dynamically as well as say  perhaps a picture here okay we can add names brown   carpeting and brown carpeting okay and so that is  how we do that and so we're going to show you how   to dynamically add these lists based on their  additional information so I'm really excited to   show that to you let's go ahead and show you how  we did that now some of the aspects of this video   including the load information how we loaded my  selection as well as add new and as well as save   as well those aspects we have covered in other  videos so I'm going to include links to those in   the description below so make sure you check that  out if you do want it because we're just going to   cover those briefly but in this particular video  I really want to go over the dynamic drop-down   list which is really powerful for any type of  work that you are doing and for any type of for   example let's say we have electrical here we could  easily create wiring and then we have different   let's say we have red and black but we want to  create blue just click in there and adding it   to the list automatically adds blue wiring so it's  very very very simple and so I wanted to make sure   we get that let's go ahead and put this back to  its proper framing since we've got this already   and then wood framing and you see how those items  change okay and two by fours okay and you notice   it's saved automatically in the table below okay  so that's really convenient and really powerful   now let's go ahead and see how we did that okay  we're gonna go into the VBA model here under the   developers tab click on Visual Basic if you do not  have the developers tab available to you simply   go in to the options and under customize ribbon  you'll want to make sure the developers tab is   selected here as so let's go into that now you'll  notice that we have a good amount of code also on   the sheet itself as well as multiple macros  here and we'll go over those briefly some of   them we'll go into more detail and others we'll go  over briefly attachments how we've attached that   picture we went over that into a lot of detail  into our attachments 1 & 2 so we're not going to   go over that too much because I did go that but  I will include a link to know about that we also   have items we have save item new item and cancel  new those as well we've gone over to other videos   into something called automated tab load and save  video where we did go over these and as well as   the tab macros and if you'll notice here we've  automated the tab ordering and I've gone over   that in another video as well so I'll make sure to  include those links so that if you feel I'm moving   too fast over some of these things I did go over  into detail we're gonna focus on the list macros   here and we're gonna focus on on sheet the on  cheat code here which is a little bit of code it   looks daunting but it's not and I'll go over that  with you I didn't buy them and basically what we   have here with the on sheet this is sheet on sheet  the items okay we have two sheets right we have   items and we have lists okay our list is basically  a list of types categories and subcategories we're   then gonna go over advanced filter and where  we add a specific criteria and also once that   criteria set we run our advanced filter via VBA  and we get to our filtered lists once we have our   filtered list we also want to create some unique  lists remember these are types categories and sub   categories and these are our unique lists okay  so it's very important that we have those lists   because our unique list change let's go ahead into  the formulas tab and go ahead and take a look at   this list and the name manager and you'll notice  that we have four list we have extract but that's   not that's automated so we can delete that that'll  become automated automatically so we have three   lists category subcategory and type and remember  these are going to be dynamic so when we hit tab   right and we're gonna use offset and you'll notice  that in the category we have two items and you'll   see the dancing ounce or those two items if we use  this formula right here let's go ahead and zoom in   list it starts with s4 and then what we're going  to do is work in the count we're gonna count all   of the items between s 4 and s 99 okay so that  means that if we were to add an item right here   or two items right if we were going to add two  items and then we were to go back into the name   manager and go back into category and you'll see  now that dancing ants has been extended to include   those items so basically what we're doing is we're  starting this is our starting position then we're   gonna simply count how many cells contain text  and that is going to be our total range and that's   really important because we don't know how many  categories how many types or how many categories   that or how many subcategories that we have so we  need to make sure that this is a dynamic list it   must change as our data change so that's important  we've done the same thing for subcategories in the   same thing for type we use the offset and account  a to keep our list dynamic so that so that is how   we've done it there and that's really important  part of it because what up basically what we're   gonna do here is we're gonna take this list  we're going to run it through an advanced   filter and we're going to set specific criteria so  for example if this is if this is type if the type   is flooring and and the category is carpeting then  we need to know all the filtered list we only have   one sub category now let me go ahead and show you  how that's done now let's say we were going to I   was going to add new and let's say we were gonna  add a water heater okay 600 let's go 60 gallon   okay and now we know that that is plumbing and but  and we know it's plumbing so now let's look what   happened to our list so we have plumbing here so  what we've done is we've created only one criteria   we've left category and subcategory blank so what  are advanced is going to do it's going to take a   look at all of this data okay it's going to use  only plumbing his criteria and it's going to list   all of the items with plumbing okay it's gonna  list all of the with the type of plumbing sconce   all the categories and all the subcategories then  we need to get our unique lists okay we have we   only have so it's gonna take this list here and  it's gonna run it through the unique list and then   we're gonna sort it alphabetically so basically  it with another advanced filter one more advanced   filter I'll show you in VBA we're gonna take this  entire list here we're gonna filter it there's   gonna be no filter we're just what I all I want is  the unique names all I want to get is the unique   niche and you'll see out of all these we only have  two unique names copper pipe and water tanks so   we're gonna take this list and that is going to  become our category right that's gonna become   our category so now you see our category has two  items water tanks and copper pipes so remember why   why because our category is our range okay that is  our category range now when we select water tanks   right we do we now we have a subcategory right  now we have multiple items on the sub category   and but we don't have a 60 gallon right we don't  have that so let's go ahead back to the list and   show you how that's done so now we have all the  sub categories listed here right because we've   run now we've run a criteria two different  criterias plumbing and water tanks plumbing   and water ticks so now the list is reduced and now  what I want to do is I want to take all the unique   sub categories and I want to put them here and  then I want to sort them alphabetically so now   I can I can either select but we don't have 60  gallon water here so let's go ahead at 60 gallon   water water heater okay and doubt it's gonna tell  us hey this sixty gallon water is not in the list   would you like to add it guess it's done right  and now you see it right here okay and you see   how it's automatically sorted alphabet clean and  I really like that feature because it makes it   really easy to find items okay and we can simply  add a picture here I don't think that's right   but it's okay and we can add a description here so  it's a super powerful feature and I really want to   show you how that then we just have to click Save  and it automatically saved to last row so I really   want to focus on these dynamic drop-down lists  because they're super powerful for whatever type   of application that you're going to use so let's  go back into the visual basic editor and let's   go ahead and see how we've done them with the on  sheet items okay now what we need to do is we have   dimensioned we've used this target count greater  than one we want to make sure that avoids air so   we always put that in we've dimensions specific  ranges okay and I'll go over these for you but   we've mentioned we have we have one range for each  and one range for the founts basically what I want   to do is I want to say if there's no type at it  right basically I would say if there's no type   at it and so we'll do that the first the first  bit of code here basically says on any change of   items save in the table below okay that means if  we change this right if I changes it's going to   save right here in the table below so that's what  it's going to do and how do we do that with such   little bit of code well it's gonna say if there's  any change to range f3 2 J 7 and B 2 is false now   B 2 is our new record right so we want to make  sure it's not a new record if it's a new item   right if it's a new item we want to click add new  okay so we only have if you look over here in a B   we only have three items B is the selected row and  this helps us with our conditional formatting okay   new item automatically we want to differentiate  between new items and existing items so when we   click add new automatically this goes to true and  that helps us differentiate okay also when we load   a form basically I want to know the difference  when we load a form you'll see everything changes   here here here here right but I want to make sure  there's a difference between a change when we load   a form and a change when we know the form like  when we change the form like this when we make   that type of change I want to make sure that type  of change is down here so the reason we do that is   so we set this form load to true and if you look  very closely this changes from false to true back   to false again and see that so basically it tells  us if we're loading a form don't save any changes   down below okay so it says if B 2 is false that  means if it's if it's not a new record if it's   an existing record and if B 3 is false and if is  we're not loading a form so if we're not loading   a form and it's not a new record and we've made a  change there's three different variations they're   on both of those then what I want to do is I want  to get the item row what row is it b1 okay then I   want to get the item column number right and the  call numbers basically when we when we save this   information here okay when used to know what  column this is column I believe five this is   column six this is column seven let me get rid  of that bug okay you see we've done that let's   go ahead and make sure let's debug that what I  want to do is I want to make sure that we have   changed the selection change which there let's  go change that to 14 okay we don't want to start   we don't want to start on row 13 right our data  starts in row 14 so let down some important thing   I'll fix that so now when we click there we're not  going to get that back okay so now back into the   VBA code we go under the items so we need to get  that item column I need to know what column I have   the row I need to know what column to save that  in and it's gonna be target row plus the target   call it plus 9 and I'll show you that okay this  is target row 6 right this is this is calm scuse   me column 6 right column 6 F is column 6 right  so plus 9 is gonna get us row so why does that   map down you see this five this is row this is  column 15 this is column six so when we add nine   we need to know when we add nine to that we get  number five I put this five there why is this   five there because I want to save this data in  column 5 so that means when we when we select a   number here and we select the number here and we  want to make a small change it knows what column   to say that in so you see how automatically has  it and we've covered that a lot in in our auto   load and save so we'll go basically that is how I  get this data automatically into the table and so   that is what that code is okay we're gonna go over  that briefly because basically I have covered that   in other videos and now I want to go in to when we  make a change to the type when we make a change ha   and I want to make sure so basically it says if  we make a change to f5 okay f5 is here okay if we   change the type what do we want to happen okay we  also want to make sure that when we make a change   that we're not loading the form remember b3 load  form must be false I only want I only want to do   what's in here based on the when we're not loading  a form right when we're not loading it so what do   we want well first of all I only want to run this  if the target value is not empty okay I only want   to do this part if the target value is not empty  so that's an important part of that and what we're   gonna do now is we're gonna set the type range  type range we've dimension this as a range and   as type 2 remember remember we set a dynamic  range type remember I showed you that so this   is going to set the type range and then what we  want to do is basically what I want to do here is   if the if we've entered it if we put something  if we've changed it and then it's already in   the list then we don't need to add it but if the  user types in something else let's say they type   in cement okay I need something I need to know is  this in the list if it's not in the list put this   pop-up right here cement is not currently type  would you like to add it now okay so I need that   half I need that to happen I need to know if it's  part of it and if it's not pull that pop-up so to   do that we're going to set the type range and then  we're gonna set this found now we've created found   to type found type it's just a range and it equals  we're gonna use the find find what are we finding   the target value so the type range so inside this  range we're gonna look for something we're gonna   find something we're gonna find the target value  remember it was cement and we're gonna use values   and hole okay so basically we're gonna set this  found type to look for it now it says if found   type is nothing if it's nothing that means if it's  not found if found type is nothing if it's found   it will be something okay if it's not found it's  nothing okay then responds okay of dimension this   has an integer true or false we're gonna put up  a message box the target value remember cement we   just had is not currently a type would and then  this goes to a new line just causes a new line   down below would you like to add it now yes or no  and then this gives that this gives the title the   title okay so let's go look at that just one more  time so I can show you again double click on this   see we've got the title type not found cement  is not currently type would you like to add it   now okay we'll click no for now and so it says  if response equals yes yes then cheat - that's   where we can't buy less C and the first available  column equals a target value now what is the first   available row excuse me row well we have to find  that up here sheet to see 99 and Excel up ro plus   one basically what that does is is going to locate  if we go back into the lists here and we go back   into our main list here it's going to say if it's  not found I want to add it right here okay what do   you see how the last one is plumbing I want to  add it right here I'm gonna put some Matt right   there so when we go into items and we click on  this and we and then we click on yes we go back   into list there it is now it's here you see that  now it's part of it so now when we go back into   items we can see cement is now part of the list  and it has also been part of the list and it's   also sorted alphabetically which I like a lot so  so that's what that code does okay so it says if   it's not then add it to the first available row on  sheet to see is the target value okay now let's go   into this now what I want to do is once I once  I have it on this list once I have at this list   what I want to do is I want to get the unique now  I want to take this list and I want to know all   the unique values which is right here I want to  get those unique and then I want to sort them I   want to get this unique then unique list so and I  want to start them so let's see how we did that so   we have here and we've cleared out this will help  clear us subcategories okay and I'll show you that   in a second why so get unique type right I want  all the unique ones so let's go into another macro   under list macros we have something called get  unique type right here and basically what that's   going to do is we're gonna with everything we're  doing is going to be on sheet 2 here so first we   want the last row as long and the last row is  once again see 9 9 9 9 ndele up that's gonna   tell us what our last row is that we need to  work with in this case it's going to be 29 so   once we have our last row we're going to clear  our previous types we don't want them and I'll   show you that we're just gonna clear out clear out  for our advanced filters here gonna clear this all   out because it's in new so I want to clear that  all out are and clear I want to clear out any   other unique types so first we're gonna clear this  list like this then so that clears out all right   it clears out all the previous types and actually  this is clear let's change that to clear out all   unique unique this okay then what I want to do is  I want to run an advanced filter there is in this   particular advanced filter I don't I'm not going  to be using any type of a criteria because I just   want the unique list so there's no criteria and  the advanced filter is super powerful so we're   gonna take the rain c32 last row we're gonna  run an advanced filter we're gonna copy that   filter to another location and what location do we  want r3 and we only want unique okay so let's go   over that we're going to take this list okay we're  going to run an advanced filter on it we only want   the unique values and we're gonna put it right  here under R 3 R 3 and then there's one little   bit of code then I want to take that same list  and I want to sort it I want to sort it starting   on our for I want to sort ascending and I want  to sort normal and I want to go I'm using last   row here but it's a the last row is much longer  but it's fine you know as long as it's over and   above what we want to sort which is fine so let's  go ahead and I'm gonna stop this right here okay   and I'm gonna run this okay that's gonna get  us a unique list but it's not gonna be sorted   yet okay here's our unique list I just remember  I cleared out but it's not sorted yet and now   what we're gonna do is sort it with the rest of  the code so now when I run the rest of this code   it's just gonna sort that list and now you see a  sorted and now since we have data okay once again   formulas sorry name manager type and since since  this offset is variable you can see it's exactly   what we want and it is this type that we use  right here okay under the data data validation   right and then it's type right so you see how that  works type okay so it's extremely powerful so now   we've got the unique list now we've sorted them  alphabetically and now it's here that's why when   we let's add let's add another one okay let's call  it roofing the thing I don't think I have that yet   roofing is not currently typed you want to add it  yes now come let's go ahead and remove that okay   we don't need that stopped anymore and now we have  roofing at the end you see so now this is how we   create dynamic and roofing has no categories right  now how do we know it has no categories let's go   ahead into that other part of the code and back  into the item sheet and now we're gonna run a   macro we've wrought we've just we've just showed  you how to get unique type and now we're going to   filter by type okay now we needed one more macro  called filter by type and what that is is let's   go ahead and go into the sheet now what we're  gonna saying is I'm gonna run a filter I want   to know everything under roofing we only have one  item and it doesn't include any categories and any   sub categories right so we've run the filter  on type and we've used the criterias roofing   but there's none so let's go ahead and and run  one on something that we do have okay let's go   ahead and say electrical okay because we have  we have categories under we have two categories   so now when you look at electrical you'll see we  have lots of item information here all right we   have awesome and we have two categories right  you see two categories and that means we've   taken the categories and we've gotten an unique  list and the unique items are two light fixtures   and wiring so our categories are two okay so our  category automatically changed to wiring and when   we click wiring now let's go back into listen  we have two criteria electrical and wiring so   our filtered list is smaller now our subcategory  is we have three items in our subcategory blue   black and red I know it's a little bit confusing  but we're gonna work I'm gonna work with you on   this so now we filtered it by type okay and we're  gonna go over the same thing as under category in   your category when we make a change to category  which is h5 we also want to make sure that we   check for if there's a current now we have  category range it's gonna say is this item a   category or not and so again it's gonna say if the  category found is nothing then our responses this   category is not found just like we saw for type if  the response is yes and here we've done two things   if the response is yes then we not only must add  the type but the category as well and I'll show   you why remember we're adding C and D here so go  ahead and look on the list here and you see how   we just added electrical boxes okay so now we  add electrical boxes right member C and D okay   if we add one more it's gonna add another line  electrical boxes and let's say plastic okay so   let's try that so electrical boxes now plastic we  have no electrical we have nothing here so let's   type in plastic let's try at least and yes we want  to add it now let's look at the list now we have   electrical boxes and plastic so now our unique  list is four types it's all this for and we've   choosen electrical so we have boxes life pictures  and wiring and we only have one subcategory items   so if we were to change this out of so here we  only have one right now we want to add another   one metal same thing do you want to add it yes  okay so now we have two options okay plastic and   metal and we also but look when we change this  we need to make that this must change as well   right because now we're looking at a difference  so now we have blue black so you see now we have   something different so we have to make sure that  if we change the higher up category the lower up   the lower category must change as well and I'll  show you how we did that okay once we make a   change we want to filter by category okay filter  by category and let's go ahead into that under   the list macros and you'll see we have filter by  category and this is pretty much the same thing   what we want to do first is we want to get the  last row of the major list and you have less than   four one exit that means if there's no items we  need to set the type filter and set the category   filter right we want to set these two filters that  is on the list you can find that on our list here   that means we want to set the criteria type to  electrical and category to wiring so J 4 and   K 4 we must set those filters that means that  when we filter this big list here right we're   only looking for items that are type electrical  category wire and you see so that our results   our results return only those results which have  electrical which have wired we run the advanced   filter so that means that here's our advanced  filter ce2 last row advanced filter C through   E and we're gonna copy that and the criteria is  going to be j-3 through l4 right once again let's   go ahead and look at our list our criteria is j-3  your l4 what is the criteria and then the results   so once again there's three parts to this advanced  filter you take the original list you choose what   criteria you want to filter it by and then you  have the results here so it's three parts 1 2   3 so that's how that is done and I'll show you  those 3 parts part 1 the original data here ok   copy to another area what is the criteria part  2 and then the results copy to range part three   here and we want unique so that is how we run  advanced filter is so powerful so please make   sure you understand it the reason and the reason  is also when we run to advanced filters I want to   run to advanced filters here one to get the data  and then to get the unique list so we want to make   sure that we clear out when we run an advanced  filter it creates named ranges called criteria   and extract when we run another one here's another  one right here I want to make sure to clear this   out okay so that's why we won't before we run the  new advanced filter and our next advanced filter   is basically what I want to do is I want to take  this information here all the sub categories and   I want to create a unique list based on that okay  so I want to create a unique list based on this   and I want to put that unique list here that's  all I want to do create a unique list based on   this information and put that unique list right  here and then I want to sort this list so to do   that we're going to take the range p33 the last  row we're gonna create an advanced filter we're   gonna copy that filter and we're gonna copy that  to range t3 remember you see there's no criteria   range right all I want to do is get the unique  list so there's no criteria range that's why so   I want to put that right under t3 again take this  list create a unique list based on it and put it   right here under t3 make sure that these headings  match exactly one little one little typing   difference and you're gonna create a bug okay so  make sure that they always match and we're going   to create a unique list so here we've created that  unique list and now I want to take everything in   t4 and I want to sort it alphabetically okay  so just like we did in the categories and now   I want to sort it alphabetically so here it's  sorted alphabetically so let's go ahead and add   one more so we could see that name let's go wiring  we have blue black and red and let's go ahead and   put it in green okay green would you like to add  it yes okay and now when we look in our lists we   have green here and it's sorted alphabetically  okay so that's how that's done and we have now   we have that option so it's extremely extremely  powerful we can assign a picture to it I've gone   over that in other videos and I really wanted  so don't forget about the advanced filter that's   really really important so we've gone over getting  two unique types okay we've done filtering by the   type and we've done filtering by categories and  then each one has their own sorting so that we   can sort them alphabetically we also have tab  macros and we've covered that but basically all   we have to do is with this macro just we just  have to set the range of the cells that we want   to tab F 3 F 5 H 5 j 5 and dimmer so with all  this that's pretty much the only thing you're   gonna have to change on that and just that allows  us to tab F 3 F 5 h 5 j 5 and f 7 ok so you notice   those are the same those are the same ranges that  we've created right here so that's it for the tab   orders the attachment macros we've covered in  previous videos so we won't go that code reset   simply allows us to speed up our application if  you have a lot of data you can use these reset   code and stop code just make sure if you're going  to use this when you stop the code within a macro   make sure that you reset it before the end of  the macro so that is very very important here   also this helps us set the tab order when we  go to another worksheet we don't want the tab   order for example we go to list I want to cancel  that tab order I want to have a normal tab order   here but when I go into this I want to have a  normal time order okay so that's why we have the   deactivate that that's why we're setting hunkee  when we deactivate the worksheet we want to make   sure it's false and when we activate the worksheet  we want to true okay that's for the tab order so   it's really really powerful I'm so happy you could  join me for this this is one of the most powerful   things they can the dynamic drop bound list can  be used in any type of an application for really   any purpose and it's really powerful so thank  you so much for joining me and I look forward   to next week thank you very much please share this  video and like it and have a great day thank you

Channel: Excel For Freelancers

Views: 100,226

Keywords: Excel Dynamic Drop Down, Excel Dynamic Drop Down Lists, How To Create Dynamic Drop Down Lists In Excel, Variable Drop Down Lists Excel, Excel Dynamic Lists, Excel Lists Drop Down, Create Drop Down List In Excel

Id: ToXa_3Gk0lc

Length: 35min 56sec (2156 seconds)

Published: Tue Mar 13 2018