 Hello everyone and welcome back to another video in today's tutorial We're going to show you a really good tip and quite an interesting one of how you can in essence do a V look up With images, so what we're going to be looking to do is you can see in our report sheet We've got two simple fields here We've got the country first and then below we've got flag So what we're going to do is we're going to create a drop-down in the country field And then when that country is selected the flag for that country will appear in the row below So obviously it's a really good one and it's definitely a makes your work a lot bit more interesting Obviously have the pictures in there and obviously can really help to illustrate your work And obviously it works with not just flags you can do of any kind of images So particularly useful if you do maybe an organization structure or an org chart And you want people's faces or pictures to be present So what we need to do is a bit of setup to do this So we'll step through it and hopefully it flows together and makes perfect sense I might also look to put a document together to call out each of these steps But we'll go through it and then hopefully it will serve as a really good way of you learning how to do this So we need to do is go into my second tab what we have here And so just to summarize that we've got two tabs What you're going to need to use we're going to have a report tab and a flags tab And the flags tab is just where we store the flags that we're going to be using So as you can see from the information I've got here I've got a flag for each of the countries that I'm going to be interested in So if you want to have more than this obviously you need to add more rows and What you just do need to do is have a Google find a flag that you're after and then just pop it into your Excel And then the format that you need to have it in is you need to ensure that each of your flags are stored within their own cell So you can just see having put borders onto my cells You can just see the border around the outside of each flag So you want each flag to really obviously fit within each of its own cells But you wanted to try and take up as much space within that as possible And then to the side of that you just got the country what that flag represents So this gives us of a table should we say of information that we're now going to use So first thing we can do is if you go to our report tab Country so this is a really easy drop down So all we need to do for that is go down to our go up to our data tab and then go across the data validation We're going to be using a list and all we need to do is select from the list source Going to our flags and it's just going to be that column of England through to Germany. So those five countries Click okay, and then we can see we've now got our drop down So we can select our country as required and obviously we can just go through those and they're working perfect So what we need to do is just go into flags and the first step we need to do is just take one of these flags So I'll just go to the top one. We've got this England take a copy and Let's go into the sabersheet and paste and I'm just going to take that flag and just center it in the center of the cell below so obviously the moment we've got a flag there, but Obviously it goes out saying that if I was to select this drop down Nothing's going to happen because we've not tied the two together What we do have Is when we click on the cell we are actually able to enter a Reference in the form as far as you can see I'm doing here and doing the equal song But we're not able to do a V look up or any kind of formula or function within this all we can do is reference A named range. So this is what we're going to be using to enable us to reference The country to find out what flag should go with that country So I'll just come out of there So the first thing we then gonna not the first thing we've already done the first thing moving the flag But the next thing we're going to do is we'll go back to our flag sheet So we've got them all in the format that we require and what we're going to do is just highlight all of this information So both columns so you can see like that and then we're going to go up to our formula sheet and What we're going to do is you'll have this section here and you have maybe like a name manager But you can see it's to find name here for me and all we're going to do is go create from selection And what this will do is it will use a selection we've made and it will make a named reference for each one of our flags and The names for the countries So create names in so all we need to do this without diving and causing any confusion is Remove the top row selection and what we want to do is have this right column selected So what it's going to do is it's going to use the right column or column B for us as the name for the country So this is what the cell reference will be named and then it will then pick up the what's in column a so use that as a cell reference So all we need to do then is go click ok and If we had to then go into the defined name or name manager It's might be for you you can see that each of those is now being created for us So if we go into England you can see that our reference for England is flags a three So that's correct if it's going to the US a four and then even if we were to go into Germany You can see it's referencing a seven So it's referencing all the cells that we require to be referenced. So if we now go to close that If we get back to our report and we'll go back to here So we can see we've got our England flag currently in that although our country selected as USA because we've now got a name a Named range for each of our flags What we can do is if I was to click our current England flag go into our formula bar and this time enter to an equal sign and then type USA because we've got a named range called the USA and then hit enter You can see that our flag has now updated to the USA So it's pulled up into as pulled as we're required if I was to then to change this to maybe Germany You can see that again, it's now pulled the German flag. So everything is connected as we desire All we now need to do is connect the dots to then say, okay Well, we want it to be looking at this cell the drop-down to tell us what flag should be populated So in order to do that because again, we can't do any formalism in here. We need to create another name range What's going to be using in direct. So if you're not familiar with indirect I have done a video on it before so the link to that video should be on the screen But in essence indirect just allows you to create as a reference from an input that you give But I said if you haven't if you're not familiar of indirect then check out that video else This video will help you Need or know what you need to know about indirect to make this work So what we're now going to do is go back to flags And we don't need to or we can stay in the report sheet actually that probably be more helpful So it's going to get back into our name manager or define name again one more time and this time we're going to create a new new range and This one we're going to call is flag So we're going to call it flag and then the formula for this is going to be equals indirect and Simply just reference where our drop-down is so for us. It's in the report sheet and it's a cell C2 close brackets So all it's going to do is it will take the value what's in here It will then use indirect to then convert it to a name reference And it will then obviously just reference that we've been picked up the value in this flag name When I then go okay What we can then do is then lastly we'll come back to our flag we've entered in here What currently says equals Germany will change this now to flag Hit enter and you can see it's updated to the USA If we now to go back to our country drop-down and select another country you can see that our Flag is now updating based on whatever country we populate And then what we can do just to tidy things up is just move and create the size of our flag like that We can obviously check it's still working fine Spain in there perfect And then what we might want to just literally do is go into drawing No, it's not a drawing where is it gone? Picture format that's what I'm looking for a picture format and then we can go into crop and Then the block crop will allow us to do is hopefully we should be able to use it just to Crop out some of the border we have on our flag and Then has that tied it up a bit if I now select another one Yeah, so you can see that you might remember or notice that fuzzy little border we had around the outside We just now got rid of that by doing a crop we can now make our flag a bit bigger Center it in the middle of our cell and then perfect So then as we now change any flag that we have you can see that it updates for us as well Top one to know actually with this is for like the USA as a prime example When you're doing the name ranges you just need to be careful that if you were to put So for USA so we use USA because it's easier name to reference if we're to put the United States What would happen is when you create that name range? It would actually put an underscore in any spaces you have in your words So actually the name range created for United States would be United underscore states So unless you updated that into your column B So it had that same name That's where you'd have a disconnect and it wouldn't know act when you selected the United States from your drop-down It wouldn't make a complete match because it'd be looking for United space states rather than United underscore states So hopefully not to cause confusion there, but in essence the key message there was just try and keep it to a single Word with your countries where possible it just will help them save you any confusion at all But they go that is how the end result is Well, we're just now done here. We're just now lost our flag. I'll note those come So there you go I hope you enjoyed that video and it gave you some good insight to a new feature you can use to Incorporate into your reports just to give it a bit more flair and give it make a bit more interest Maybe for the recipients using it obviously it makes it look a lot more professional And obviously a lot more technical when you incorporate this and hopefully once you get grass with it You'll find it's actually quite a simple function to start incorporating into your work If you haven't already, please do subscribe to the channel Hit that bell notification button so you're notified of all the future videos that we bring out if you did enjoy the video Please do give it a like it's greatly appreciated by us and it does really help out the channel Thank you very much for watching and we shall see you in the next video