Today let's talk about searchable drop-down lists in Excel. I already have a video on
how you can get this done. It's good for cases where
you need it once in a sheet, or have a central filter
for your Excel report, but it can't be replicated
to multiple rows. Since that's the most common
request I get after people watch that video. Today I'm going to address that. I'll show you an easy method you can use to replicate your
searchable drop down lists to multiple Excel rows. Let's get to it. This is where I want to
create the searchable data validation lists. I want them to apply from
row five until row 19. Basically anywhere here, I
want to be able to type in some characters, click on a drop
down and get a list of names that include these characters. Now this can be at the beginning, the middle or the end,
that shouldn't matter. The complete list of customers is sitting in the master data tab right here. In addition to their names, I also have the company
they work at. Then notice this is formatted as an
official Excel table, which is called, Table Customer. I'm quickly going to bring you up to speed on how the original solution worked, because that solution
is going to flow into our today's solution as well. So that didn't use any VBA. Instead, it used the new
dynamic array formulas that are available in Excel for office 365. The problem with this solution though, was it only worked for a
single cell, you could have replicated to multiple
cells, but basically, you typed in some characters, and then we use formulas
to create a data validation preparation list that was
later used by a drop down list. So basically, we prepared
the names here that we're going to feed that data validation. So let's do that really quickly
by writing up the formula. Now I'll just work from the inside out. So the first thing I did, was
to search for the text that I just typed in, in this list here. And to do that I use
Excel's search function. So, the text I'm looking for is the text that I just typed in. I'm looking for it, not in
one cell, but instead in the entire customer column
here, start number where do I want to start looking?
Well, I want to start looking from the first corrector that's
an optional argument anyhow. So close bracket, press Enter. What I get is a list
of errors and numbers. Wherever there's a match, I get a number. That number can be different, depending on where those characters
that I typed in, start. So I typed in RO in this case, it starts From the fifth character,
in the other cases, it starts from the first character. And wherever I have hash value, it means it didn't find a match. So my next step was to turn
these into a list of true and false values. So wherever there's a match,
we're going to get a true and wherever there isn't a match, we're going to get a false. The step after that was to filter for the true values, by using
the new filter function. The D array is the list that
I actually want to get back, which is a list of my customers. The next argument is include arguments. So what do I want to have included? By default, this includes
anything that results into a true. I already have that outlined here. So anything that will
result into true will be included in my final array. The last argument is if empty,
so if no match was found, wondering when it would get
back, I'm just going to type in, not found, close bracket, press 'Enter'. That's my list. It's restricted to
whatever I type in here. My end result was to get a
data validation back, right. So let's set that up. Let's
go to data, data validation. Instead of any value, I want to list. And I want that list to
take a look at master data. D2 that's where my list starts. But I'm going to use the new
dynamic array syntax, which is the syntax for spilled
ranges, the hash sign, so this will give me the
entire spilled range back and then click on OK. If I click on the drop down here, I get a list of names that
include these characters. But there is one little
thing I need to fix. So if I change this to
'L' and click on this, I get an error that
the value doesn't match to data validation restrictions. So there is one setting, I need to adjust in my data
validation, to get this to work. So let's go back, to data
validation, on the error alert, uncheck, show error alert
after invalid data is entered and click on OK. Now if I type in an L and
click on this, I get the list of names that include L. And if there is nothing in there, and I click on the drop down,
I get the full list of names. And if there is something
typed in, that doesn't exist, I see, 'not found here.'
All that works well. But the problem is that
this data validation is only pointing to B5. How can I apply this
to the next cells here? My first idea was to use
dynamic named ranges. The problem that we currently have though, is that we can't use names that use dynamic array references
inside data validation. So let me quickly demonstrate that. Let's go back to our RO here. I'm just going to reference this here. And what I'm going to do, is to create a dynamic named range out of this, but just so that we can visualize it, let me take this here and
paste this right here, and switch this to this
cell and press 'Enter.' So basically, whatever we change here, we can see our spilled range here. Okay, now what I'm going
to do is to create a name out of this, let's just fix the E5, I want to fix the E but not the 5, because my idea would be to
apply this to the other cells as well. Now let's copy this and go
to formulas, Name Manager, and create a new name. I'm going to paste that
formula in and call this DV for data validation and click on OK, that formula that I have
here is now a part of a name. So if I close this, delete this, and instead replace this with
DV, I get the same thing. If I change this to RO,
that formula works fine. If in the next cell, I would
have, let's say, Miller, and I'm just going to copy this down. And now I'm going to move this down, okay? Because imagine this is
inside the data validation, it will work fine, right without problems. So in theory, I should be able
to create a data validation that uses these names. So if I go to data, go to data validation, go to settings here, list,
and type in DV, click on OK. This is the error that I get the source currently evaluates an error. Whatever you Pick, it's not going to work. So then I thought instead of using a name, how about creating different
data preparation lists for each of these cells? Well, let's see how we can set that up. Let's go back to master data. Now the problem is I
can't just pull this down, because this is going to
be a spilled range on top of another, just see if
I just pull this down. These are going to be spilled ranges. So I can't do it like this. But what I can do is
to transpose this list. So I'm going to put this
inside the transpose function before I pull this down. And now, I can pull this down. So as, to as many cells
actually as I need that data validation, think I had 15 cells there. So let's pull these down two more. And that's now separate
data validation list for every single cell, so notice that here, I get the entire list because those cells are currently empty. Now, one thing I forgot to
do is to sort this as well 'cause it would be nice to have a sorted, filtered data validation list. So I'm going to put the
sort function inside there as well as I can sort that filtered list. And let's just drag this down. Okay, so let's check all this. Let's first update the
reference we're using for our first data validation list. 'cause remember, originally,
this was a fixed reference to D2. So I'm going to keep the D fixed now. But I'm not going to fix the
2, because as I copied down to data validation, I wanted
to reference D3, D4 and so on. Now, let's click on OK, now
that we have our first data validation set up properly, I'm
going to copy this, highlight, right mouse click, paste
special and select validation. As we just want to copy
the data validation lists. Okay, so now let's check for Miller. I have Gary Miller for RO, Robert bloom. Let's say if I don't type in anything, I'm going to get the complete list. And if I start typing like
ST, and then click on this drop down, I get the list of
names that include the word ST. Right, so that data
validation list is going to work on all of these cells. Now, one last bonus formula
is to get the company once we have selected the customer. Now to get that set up really fast, I'm going to use the
new x lookup function. Look up the customer
here, where I'm looking it up is in the customer column. What do I want to get back
when we get back the company and in case it's not found, because I haven't finished typing in the name or the name doesn't exist. I'm going to put nothing there,
close bracket, press Enter. And that's it. I don't need to fix anything here because I'm using table references. So I'm going to copy this down, and I get my searchable drop down list on every single line here
together with a company that that customer works
at. Well that's it. That's a simple method you can use to create multiple searchable
data validation lists in Excel on the same
sheet below one another. I hope you enjoyed this video. If you did, give it a thumbs up. And if you're new here, if you haven't subscribed to this channel, consider subscribing.
