 Hello everyone and welcome back to another tutorial. In this video, we're going to be looking at searchable drop-down list So the video is going to be in two sections as such Mostly we're going to be firstly looking at how to create the searchable drop-down list And at the end there we're going to be looking at how you can create multiple drop-down lists So you'll see as we move for this video And we do the first search down drop-down list that searchable drop-down list You'll see obviously the limitation that it could have in its default form And we'll just show you how you can use that if you want to be having multiple drop-down lists within your report So the basic formula or not formula set up we have for today is we've got a report sheet where we're going to be sharing Obviously the multiple drop-down lists and then we've got master data. What sits behind that? So master data is just a customer and company column and we format this into a table what you can see by obviously click into it or the table options here and You scroll down and you can see also you've got a corner formatting there to show you that it is actually a table And we can see the details for the table is this is called doesn't quite fill the page, but it's cool Oh, there you go. It's called a customer table. So that's what we'll be referencing for that It's also worth noting. I'll just put the website on here of generate data.com This is where I've generated this random data that I'm going to be using for this tutorial And it's also a great resource for you if there's other data you want to generate It's all completely just random So there's obviously no actual data being used and it's just a really good resource for getting that Generating random data for when you're trying Tutorials such as this or when you're just doing your own work because obviously appreciate these formulas need data for you To actually be able to try them in the first place So the first thing we'll start by doing is building out obviously the validation prep In column F here So this is where we'll gradually build a formula to show you how the dynamic list is can be formed and we'll be using data Validation to provide the drop-down list in the report sheet So we'll further delay will jump straight into it Although one delay is if you're new to the channel Please do subscribe to the channel and hit that Bell notification button so you're notified of our future videos And also if you do enjoy the content in this video Please do hit that like button something only not only appreciated by me But it's also very beneficial for the channel as well, and if it is your first time checking that channel Thank you very much for stopping by So here we go. Let's jump straight in. So the first part we're going to do is a search So we're going to take the value what we've got in our report sheet And we're then going to use that to search our customer list to pick out all the values that match our In criteria that were invented So what does it start off with is I'll just put in here some initial initial initial initials So two characters that could be the part of our search So we want to look for a customer and so far we're putting be so we want to pull back all the customers that have be within their name So back in the master data in our validation prep we can start entering our formula So we're going to do equal search and We'll open brackets and the first thing we need to do is obviously search for that value So I was going to go back into that into that customer sheet. So for us, it's report B a Once I go back into the master data, let's get rid of that I don't need to select our customer table So that is just column a and as you know if you hover at the top of that column of a table You'll get this downward arrow select that and then obviously will select the column within the table Close brackets Don't need to enter anything else here hit enter and you can see because we're using the new dynamic arrays in office 365 So you need to have office 365 for this to be working for you You can see it's done a spilled range and what that basically means is it's gone through each of those Values in our list so you can see each one of these values at the moment corresponds with that name in that row And we've got some options here So we've got the value error if obviously those initials or that be was not found within the name But where it is found so Bell he see row 27 It's the first it's returned number one and what that means is that search term is appeared in the first character of That name for Sasha Bell one just below what is very well placed to give the example You can see it's given number seven because the bee doesn't happen until character number seven and so on and so For for all of our options available there So we want to now just sort of finally tune this so we're going to turn try and rather than have numbers and the error We want to have this into a simple true or false in order to do that We can just use our yes is number function So I'm just going to revert to our function atop it and type in is number Open our brackets and then we just need to enter another closed bracket at the end there Enter and you can see it spilled and updated that for all of those values So what we now have in each corresponding row is a true or false value to tell us if our search value The bee will have done so far is present in that row or in that customer name being more specific So that's great But we want to now build on this one more step because it's great as it is to have the true or false All we really want is the list of names that our search is applicable to So we only want the list of names that have the initials B and E in them in order to do that We can use our new filter function So you might have seen this already on our channel in previous videos But if you haven't we'll quickly go on to it now and you'll get a brief idea of how that works So again another little amendment to our formula top in and I should have said at the beginning I was going to be building this out gradually but we kind of built it from the inside out So you can hopefully get a better understanding of how the function is working So what we need to do now is use our filter. So we'll type in filter open brackets So what is our rate? So we want to get this is our array the customer name So it's had to select that table one more time and this is the rate This is the array or the range of names that we want to filter and The next part for include so this is going to well This is where you'd have to put some logic in to determine what names you want But by default what's going to do here for us is it's going to pull through only the names that meet the true in our second Piece of criteria for that is number So that's where I can perfectly well for us as it is so we don't need to do anything there All we need to do is jump to the end and where it says if empty if I was to add that comma there And this is simply if there's no value there or we can't get a matching value What do we want to return? We want to return not found So this is predominantly only come up is if we entered a string or initials into our report and There was no names that had those values within them And we get this message pop up that said not found so we know there's there's no values in there for us or matching our criteria Once you hit enter you can now see you've got a list of names there what match off such criteria So we've kind of achieved what we've initially set up to do if I was to go into our report game And let's say let's select some different names. So let's even just say s Just a letter s go back to master data Quirky's a few that you but you can see how it's updated Maybe I'll just do another one just to try and get a better idea CL Yeah, there you go. So you can see again. It's updated So it's now only shown as the names will have the characters of CL in them as a as a joint together So from this information we've got already we could actually now go and do our drop-down list And all to do that. We're just going to use data validation So we can go into our data tab and go into data validation We want to show a list because we want the list of obviously the names and then our source is going to be If I just select that go to master data, it's going to be this first one here. So f2 for us as you see So what we're going to need to use here is the new dynamic Syntax for spilled ranges and what that is simply is the hashtag So what this means is our data validation will first reference master data f2 So obviously this one here what says Cameron in it at the moment But by using the hashtag it will just spill to whatever that range is so we can see the number of Names here at the moment. So it's going to just incorporate all of those names into our list for us once you do, okay You can see that we've now got a drop-down list that gives us all of these options in here Well, every single name One change that we do need to make I think when I quickly jump in and start showing the search aspect So if we were to put CL You'll see that we'll get an error message So this is not ideal because we want to type in a search string and to return all of the matching names in our list So therefore we need to make one little change to our data validation to allow us to do that Let's just go cancel We return back into data validation and You can see what we're into here so far, but we just need to go into this error alert tab And we're going to just untick this show error alert after invalid data is entered So basically the reason we're getting an error is because CL was not a value in our list But because we want to use it search criteria, we just need to untick this just so it's going to allow us to do that in the first place So if we do okay By default our list is going to show us every single name that we have in our list There's quite a few of them But if we were to enter initials CL and then do our drop-down list We'll only see the names that are applicable to that search and likewise the first ones we use I think would be E You'll now see only those that match them work be E So that is how you create a dynamic searchable drop-down list in Well, I'll say the word dynamic. Let's just make this a bit shorter That is how you can create a searchable drop-down list in Excel in using Office 365 The only problem with that is you're limited in the sense that it's only going to be available for just that one drop-down list But what we want is we want to have a drop-down list for each one of these cells here So what is the solution to allow us to do that? Well firstly when we go into our master data, we need to do a quick change here because in essence what we need is We need this spilled range of obviously the search names for each row of our customer names So let's just make that a bit easier. So in our customer range, we've got one two three four five six seven Thirty so we have 13 potential drop-downs here. So we need to have 13 of these validation preps so that there is one validation prep for each customer row and I hope that makes sense, but if it doesn't then hopefully it will as we move through this example here now In order to do that we We obviously need to have like say we need to have one each But we can't just copy and paste a validation beneath each one of these Else we'll have a problem of spilled ranges. We'll be spinning into one another So what we need to do is rather than have this in a column format Going down horizontally We know going vertically. Sorry. We need to change it into a horizontal spilled list So it goes across here. So then we're able to create each one of these for each row In order to do so we can just update our formula here to include transpose So for those who don't know transpose will take Some information like this we're having the column format and it will transform it into a row format And you'll see as soon as I enter it in here. So all we need to do is just go transpose here Open our brackets and just enter closed bracket at the end there And you can see now rather than this spinning down Vertically get it right this time. It's ours going to spill horizontally for us And having just done that I can notice that our data is not in alphabetical order So let's just make that one change as well. So just inside of transpose. I want to do sort Open brackets and then just enter another closed bracket for us at the end here And you can see we're now got a more presentable format here that we can see them in alphabetical order What just makes that a lot better just when working with data anyway, really Now that we're done that we're able to actually copy this down for each of our rows So we had 13 customer rows in total. So all I'm going to do is drag this down two three four five Something like that So what we've got we've got reference b8 it was so we want down to b20 I'm going to try to look it up then a few two many four three two one There we go b20 so you can now see we've got a Validation list for each of our rows and our data For that first one we've got the initials be you can see our list has already been formatted for us So we've got these five names here But because all of our other drop-down lists are currently empty That's why we're able to now see the full extent of our customer list in this transposed list So having done this we've built all the back-end data that we need So we can jump into our report sheet one more time and show you how we can now do this drop-down list to reference those cells We need to make one quick change. So if we're going to data validation once again Into our settings So at the moment, obviously it's fixed on this range of f2 with this build hashtag there We just need to remove these dollar signs. So obviously it's not fixed and we can copy that down and let's go Okay And then now all we need to do is control c on that current drop-down Go down as the far as we need to go in our customer range We're going to pay special Validation select okay And now when we go into these lists, we should be able to see what we have so perfectly at first We've got the lists that contain all of our names If we were to go and type in here cl And then do our drop-down You can see how it's now filtered to those and let's go to one random ones go around 13 and let's go uh d a You can see that how this is now updated for all those applicable to initials of d a And some of them at first might know why I might get in da because you might expect to see david or damien But obviously you can see the da is at the end or throughout this text here as well If I was just to change just to put maybe david And Ah another good example here no david's on this and that's why we then see this value of not found So a great demonstration an act or anything actually showed how to do that And so we now got names available to us so we can go through and select these either Just like this if we want or we can start typing and we'll pick out those only applicable to those as well So one last thing because we have got the company uh column on here as well How can we pull through the applicable comp company for all these names? So let's just actually put some names in here just so it actually does look right Um get rid of david and we'll put in here abram So we want to pull in this the corresponding company for this individual Very simply we're going to use another new new function. Uh, if you weren't aware of it, it's called v Not v. Lookup. Uh, it replaces you can say v. Lookup. Uh, but this is called x. Lookup So it's sort of more and better improved. Do I say, uh, but obviously I leave you to decide that But anyway, we'll be using x. Lookup. All we need to do is enter x. Lookup Open our brackets. So the first thing you do is enter our lookup array, which obviously is our custom name here We want to look up the customer name in our column our customer column So it was just going to be that one there We want to return a value from column b, which is the company column And at the end here, if not found we're just going to show it return a blank because in theory we could be still Populating at it in our data. So we don't want to start trying to pull through things Pull through errors or whatever if um, we're still busy trying to type the information in the first place If you haven't used x. Lookup before There you go. There's a summary on the page basically how to use it and the criteria you need As you will see it's a lot simpler than the v. Lookup function But we have done another video that looks the x. Lookup in depth So if you want to check that out by all means check out our page on youtube You'll find that tutorial and that'd be hopefully give you the insight you need to learn about x. Lookup Once you've done that enter You can see it's now called that company through for us and we'll just copy this down All those rows Like so and you can see it's only populated there So if we now go and do select a code customer from here and let's use our new search criteria s So I think of ones I haven't used um s a Here we go Sasha bell you can see it's now going to pull for that company for us as well So a little bonus on there as well in addition to looking at the searchable drop-down list So I hope you enjoyed that video And it would give you the insight of one how to use a searchable drop-down list in xl And also how to extend that so that you can have multiple searchable drop-down lists within the same report If you did enjoy that video, please do give the video a like as I said the beginning of video I greatly appreciate it because it shows me the content that you'd like to potentially see more of And it also does help that all important youtube algorithm If it's the first time to channel or if you repeat viewer, please do subscribe to the channel And do make sure you hit that bell notification button so you're notified of our future videos Thank you very much for watching and I'll see you in the next video. Oh and one last thing before I go Links to this document that you have been working on throughout this tutorial that can be downloaded by the link in the description for this video So I think that's everything. Uh, thank you very much once again. And yeah, we'll see you in the next video Before you go, don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information And obviously as always, don't forget to subscribe and hit that bell notification button