 Hello everyone and welcome back to another tutorial in today's video We're going to be touching our subject We looked at our previous video and if you haven't seen the previous video We'll put a link on the screen now because that's a again using what we're going to be using dynamic ranges Very useful video as I say, so I'll put a link to that previous video on the screen now So you can go check that out as well, but what we're going to be using dynamic Rangers for in this video is for populating our drop-down lists So many of you would have seen our previous videos are quite a while back now of how we can use drop-down lists in our data to enforce the values that people select So as a quick recap, let's say In this column here in column D. We simply want to do a yes or no response And obviously take this our context obviously it says review at the top there And this would not probably want a yes no in here, but we'll just do that for example purposes So what we can simply do is we just let you go into our data We go to data validation And then we can select a list option and then in here we could either select a static list Or we can simply type in yes, so no like these are options And then that gives us this drop down in that range to select either yes or no And if someone tried to put something else other than that so the spell yes wrong or no, obviously it's not going to allow them to do it and The other way you can do it as I just saw briefly touched on there is you could also have your values here In an actual range and then we can actually go into Reference that range. So let's go about data validation And this time we're going to select that range there And you can see it does the same result It just means that if we wanted to change our values we could simply do it here Rather than having to go into the actual settings for it The limitation with obviously entering your values whether you do it Into the actual pop-up self or creating the list as I've done in this second example here Is that if you add new values or you want more values to be available? So maybe like the option of maybe What you'd have to do is go back into your settings back into the data validation and In the wrong air range So you'd have to go back into everything And reset your ranges so that you're now looking at the new range So for us it's going to be now these three rather than just the two For that new value to pull in well again It's not too much work that you have to do but if you're sending this This spreadsheet out for other people to populate or Any other numerous examples of where this is called fall over? It just means that values might get updated or added to your list But they're not actually getting pulled into your drop down so it can just cause confusion basically So the way to get around that is to have a dynamic list And that being as soon as a new value is added to that list It's automatically picked up by our data validation and it's going to be available for the user to select In order to do this and again like in the previous video We're going to be using tables in order to give us this dynamic range All I'm going to do is just get rid of the data validation. We've got here at the moment Let's get a clear rule. I'm okay. You can see that drop down is now gone And just for tidiness, let's just get rid of these values that we've got here as well Perfect so you can start fresh What I'm going to do is go into our second sheet here. I've called it values, but it really doesn't matter what you call it And we'll just call um, let me give it a heading of let's say Yes No, it's just really it makes it easy to identify what this one's relating to and then we can just type our two values So we've got yes and no It's worth noting that whatever one you put on top is going to be the first value that appears um Might be of importance to you, but it just sometimes helps when you're using If you want to enforce a default value a suck So what we're going to do once we've got our initial values there We're just going to select those three rows and we're going to select the table And table is found under the insert tab if you've not already on that sheet And you can see that it's picked up our range and we just need to make sure that if this is unticked We just need to tick this just so it's aware that the first row is actually your headers So we don't want to actually use that as values. It's the headers for that table and go okay And you can see there's some formatting for us so we can now see it as a table And for us we can just see that obviously the yes or no The header is in that nice dark blue and that just stands out and be different from the actual values below it And from selecting in there, we can see that it's called the table name is table two By default if you've not put any tables into your spreadsheet to start off with it'll default to table one But for us, you can see we've obviously been playing around with other tables. Therefore, it's automatically Gone up in iteration. So it's now coordinate table two So that's our values there If we go back to our review sheet and we now want to enter this data validation All we can need to do is select our desired range. So for us, it's just these five rows that contain names And then we go into data data validation And we want to select the list and this time we click into our source And navigate to our value sheet And you can see that we've got our table in here. So What you might think is we're just going to select the range, but actually we can't do that We actually have to use another formula. So all we're going to do is type in here in directs open brackets And do a quotation And then we know it's I was going to say we need to know what this table is called. I know it's called table two We're then going to do a bracket. So the square bracket And then we need to put in the column Header or column title for for that particular column that we want to source values from So for us, we know it's table two and the column we want to source for our values is yes or no So you can just type in here. Yes or no Close our brackets do another quotation and then close our curve brackets And then select okay, and you can see we get taken back to our review sheet And now what will happen is when we go to our drop-down, you can see we've got the yes or no options So it's pulling our table or our desired values as required The real benefit now comes if we go to our value sheet and we say we'll actually what another option of maybe Then we can then come back into here and we can see that maybe is now automatically picked up as one of our values So it's a real dynamic way of working and straight away the opposite is if we go to values and we get rid of maybe We can see that it's been automatically updated. So we now we've only got a yes or a no in there So again a real great dynamic solution The reason we put review here is I was actually going to do a different example here rather than yes or no So we could just go through that and that would be let's say Low Medium and high So again, you can see another way never great use of this as we suddenly change in mind Do you want something else? We can go into here and we can now see we've got the lot options of low medium and high And what my intention was to do is to say, oh, we're going to give each one of these people a review and we can give them a medium Low or high review obviously based on maybe what their performance or how they've been working is Like this and you obviously they have a benefit or not the other benefit And then the example is going to then mention that if we wanted to change this and then maybe have Let's say very low low medium High very high Like that And then you can see that we can now got these selections in here as well And I did touch on I think earlier But obviously the order that they appear in the drop down is the order that you type them in your table list So that's just one thing to mention because you might want this sort of Presentation in terms of obviously lowest to highest if you're doing this or drop down When the person has the selection So I hope you enjoyed that video and it's gave you insight of obviously how you can make the feed for your data validation dynamic Again as we touched on it just makes it a lot simpler for when it has been multiple people multiple people updating this Or if you just to make sure that all your information has one central point Because what you can do is obviously the more drop downs you have the more tables that you can add to your values sheet Often as a practice what I often do is we'll do a separate table For each value. So let's say in this one. You did just want your let yes or no what we could do is table No I'm just trying to think of a unique title header. Yes No All we need to do is go into here Table table has headers. Okay That's called table three So what we could then do is if we then wanted this one to be a yes or no Let's go data. I can't remember where it was then data validation We want a list And our source for this one is then going to be After we go back in here, just make sure you've got the right name right So we want to do indirect Open quotations table three They for now have our brackets table new and again, don't use these column headers as examples For you to use it's just examples for the purpose of this tutorial because they're not very well named Go, okay, and you can see now. We've got a yes or no drop down here as well. So we can put yes No That was an option so that you can now see how it would look if you're going to add more Tables to your data so you can see each Each drop down or each drop down list is its own unique table. So what table two and table three Leave them separated by one column obviously So there's no confusion But then this is how I would build out a sheet of multiple drop downs to make sure it is more clear So we can see obviously a dedicated sheet for where all our values reside And then there's no it's not too messy and we know exactly where you need to go to update those values So I hope you enjoyed that video if you did please do give the video a like It not only shows me the videos and the content that you want to see more of it also does help out with that youtube algorithm And lastly if you're not done so already Please do subscribe to the channel got lots more videos coming out at the moment we post weekly But I really want to try and increase that frequency But by subscribing and hit that bell notification you'll be notified as soon as our new videos do come out So thank you very much for watching and you should 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 playlist 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