Hey, sheet shakers. It's the big sheeter himself, your friendly neighborhood spreadsheet specialist. Today we're gonna talk about how to create a self adding or self-healing dropdown menu. This is probably one of the more unique things I've ever come across and done because it saves a bunch of time, but also like acts like magic.

. So here's what it is, and then I'll show you how to build it and we'll go through some use cases for it. Here we have a set of places to go. We've set all of the continents and this continent has a category. Has the continents. We can change, we can change it to Europe and see how what happens is I can add.

Antarctica, I can add any I want, and it becomes available here. . So if I go, oh, we don't want to go to Gibraltar, we want to go to Hong Kong, and then I want to go here, Asia, see this automatically adds Asia to the list and then the dropdown menu. I don't have to go and update the dropdown menu anywhere.

It is already updated. So I can add some more cities like Beijing Carta. Right. And then if I want to add go, Chile is in South America, so if I want to add down here, I can just, maybe I want to read. Contextualize these. Maybe it's not continents, but it's countries. And then I can say, okay, and automatically the this list is being updated as I change it.

This is.

right? So we can see that this list is what is validating this dropdown. And now all of these are added without me having to add these to it. So how is this done? Well, this is basically a unique list over here, and it takes all of the things in the row sea from five down, and it creates a unique list here.

And then this data validation we'll show you here is the list from a range that is. , E five to E 10 over here. So let me start from scratch and I'll show you how to build this. So in this use case, we have like a bunch of places we wanna go and we're gonna rank them, like we're gonna rank them, probably not all of them, maybe just one.

We wanna do two and we wanna do three. But typing, we do, we wanna make this a validation, a data validation dropdown menu, right? Cause we wanna set, we wanna tell other people, Hey, you can rank these. So we'll do here, we'll do rank. Here and, and usually what we can do is do 1, 2, 3, right? You can say data validation.

It's gonna be here. We can even select this here, select that. Let's select all the way down to here so we can add more save. And now we have a drop domin. But it's not gonna change. If I type in a number, it's gonna give me an error. Right? But sometimes we want to change this. So let's change this to equals unique.

Let's see, what is that? Five two C. Now we'll just delete these two and it automatically grabs those. Let's put the one back there and it automatically enters that there, right? So now we copy and paste this. Let's delete that, make it blank. Put the two back.

So now we have nothing here, right? So we just add two, three, and now we have those in the available. So we go over that two, three, let's put that there. You want to delete those, but the, the pitfall you're gonna run into is that now, even though this is like self adding and self-healing, anything you put into.

If you mistype it, anything you put here, it's gonna automatically be added and you won't really have data validation. You'll have a dropdown menu, but you won't be able to validate and, and not let people enter certain things. So that's one of the pitfalls from it. But this ends up being, in some use cases, very, very important because say we have, or we wanna rank these, but what if it's like a group?

And we really want to get, like, we want to get Josh rank and we want to get Abby's rank, right? And we wanna get a bunch of different people's rankings, but like maybe they don't want to rank by numbers. So what we can do is let's do this. We'll do Josh Rink and Abby Rink. . We're gonna create the same thing for Abby Unique.

We're gonna take a unique of all of hers. Put it there. We are going to use data validation list from a range, and our range is going to be here. Nope. Here. Okay. Save. Nope. E five E.  safe. There we go. So now this is looking at this, and this is looking at this. So column D is saying, what, what can I pick from f and F is like, what can I add here?

So let's say most wanted,

least wanted, it's they're adding. And you're gonna get a little bit of an error. Because probably data validation. Oh, it's cuz I put it in the wrong. So F for F 15. There we go. Save that. Now that's working.

You know, maybe, oh, we have to edit this now.

Now anything we add here is gonna be available in our dropdown. Oh, we have to copy paste the validation

and now they're in our dropdown. So one thing you might want to double check is that, you know, these are, Things you want to be able to edit, right? If you have, if you absolutely know the three things you wanna include here, or you know exactly what options you want, then you never have to edit this again.

What this allows you to do is edit as you go. If you don't know the categories or you wanna change the categories, you want to add categories. If you forget categories, if you're setting this up for somebody else to enter, and you really don't know what categories they're gonna pull off, you can create this so that they can have a dropdown automatically, so you don't have to go back in and update this dropdown.

for them. Again, one of the pitfalls is if they make any mistakes or add anything they don't want to. It's very hard to figure out how to not allow that, right? Like you have to see that it's misspelled you, you don't have a data validation there for you. It's not really for data validation. It's really just to get this really cool dropdown menu if you want, and that's how you create a self adding drop-down menu.