Countries here all the way from Afghanistan down to Zimbabwe so basically autocomplete gives you the ability to quickly either an automatically type start typing the name.
And then have a complete here's an example let's say we want to type in Armenia AR M and then you see how it just auto fills right and so that's really convenient that's a pretty common.
But does a lot of scenarios in which it's more difficult for example if we were to put the country drag it up here and its own way from the list it would not automatically fill it so.
The only fields adjacent to the list would come up so if we try to type in that same Armenia it's not going to come up and so how do we get that there well it's something that I call break the.
Chain and it's a chain that as long as we don't break it it's going to clear and that chain it can be text in the cells and so as long as we have adjacent cells with text then it's going to work.
So if we put an X here connect here we are then creating a chain it's basically a link to the list and that link helps us to link it in Excel it's not only explained very well so now if we type in.
Erm it automatically comes up again so that's a great feature and so as long as we're in the same column and there's a link now if we just break one item in that chain right if we break that right.
Remove that we type it in it's not going to work see so that that link that link is critical we need to have that link and that link can be any text and if you don't want to have exit there even a.
Simple space even spaces are good enough so here now we just we've replaced it with spaces and that works just fine so that's a really important part and next we're going to move on to.
How to create autofill when your list is in another sheet so for example here we have our list of countries button when we go to the form sheet here in the next year we've got names and we've got.
Country and now we can just put in our
Name and put in a city so we are Los Angeles and let's say we want to put in the country of the United States okay so nothing's going to happen not.Going to be able to get it filled out right and we're not it's not it's not coming up nothing is coming up and that's the reason is because we don't have anything in that same column so we.
Have to make sure and one of the things we do is instead of just copy a list over what we want to do is we want to copy and paste the link over and this way any changes in our original list and.
We'll do that all we have to do is click on the first one and hold shift and ctrl and then the down arrow okay and that's going to go all the way and now ctrl C copy okay you go back to forms and right.
Under this what we're going to do is we're going to actually let's go a few spaces away we're going to right click and we're going to paste the link link okay and now we've got the link and.
Still it's not going to work because we still have spaces right we've left spaces there so it's not going to work to create that link okay and basically we can do it directly under we can do it.
Route use X X and X okay now we've got the link now it's going to work okay and we want to go United States and now United state have a lot of book we want to find it quickly alt and then the down.
They are Alt key alt and the down there that will get us closer and the reason it didn't come up because we have two different countries that beginning United States so that's why I didn't.
Come up automatically okay so this gives us the ability once again let me go through that again if we start typing in an a let's say we start taking a test right alt and then the down arrow.
Will get us to the asses and that's a
Really great way to find it now let's say that we need we need up like let's say we need a blank here right now it is a blank here any type of.Link any type of linking from any cells that come back around are going to help us so for example we could even go as far has to go if we can create a long chain as long as there's a chain it.
Doesn't matter how long it is so we can go down down okay we can create that link all the way here and that's still going to work okay now just to be sure I'm going to delete we're clearing.
Everything out of these funds and so our link is now all the way around I put in the wrong let's go ahead and bring it down to country there okay okay so now we have we have a link okay and it's.
Gone from country all the way around you see that link and now it's going to work now FM Samoa yeah works now let's break one let's break one chain in that link and.
See if it works as a.m. Oh nope doesn't work okay so that link is critical okay can be as far away as possible can be any cells to the left or the right or above or below but it's got to be linked.
To that list one break in the chain and it's going to not going to work okay so that's how we would do it if we're in a forum okay and now what if we have it in the table here's a table okay now we.
Have many many blank spaces we can use the same same principles oh there's one other thing I want to show you now this is all nice but we don't want to show the list maybe we want a nice clean form.
We have a few options but we can do the shift control and then down there that'll go up so 255 in the last row so we can hide these rows okay simply by right click and hide it and it's still.
Going to work okay it's still going to work and there's another way we can do it okay so that works just fine and let's say we don't want to hide them but maybe we want to but what we can do is.
We can also hide them simply by changing the font tone okay go down and assuming your background is all one color in this case we have two so it would really happen but we can simply change the font.
Color to the font of the background and that will also hide it so we've done that we've hidden it simply by creating the same font color in the same background.
Color so that works really well and the same thing here back in tables now we may have a table in which we want users to automatically Phillip and we can do the same thing will start out we'll copy.
That list we've already highlighted so we're going to copy that go back to the tables and then somewhere below it or billet it basically we're going to paste that link in okay this way the reason.
We're pasting a link right is because if we make any changes to the original list it's automatically going to be updated long table so that's a really important feature okay so once again if we try to.
Enter it's not going to work okay but as long as we break now let's create a chain okay and we can go okay so here's text here's text is text text it down here we can create any kind of a weird.
Any kind of weird chain as long as there's a as long as there's a link okay and so we come down here let's create a link all the way down and there's the chain you see how our chain is connected.
Okay and now when we try to type in someone it works just fine let's try another one and okay and gola works okay so basically that's the idea the idea is to create a chain if you don't want to.
See access just type in spaces but the idea is as long connected to to the list you're going to be able to auto autocomplete it quite quickly also keep mind if you have a very large list of.
Maybe one to two or three thousand names those names that aren't the bottom list are going to take longer you may see here although this is a relatively short list but if we type in Zimbabwe it's.
Pretty quick here but the names towards the end of the list tend to get slower and that depends on your computer speed it depends on the length of list so the laws a lot of dependencies on that but.
Basically that's the idea of autocomplete and Excel doesn't really do a good job of explaining that so I wanted to make sure you guys understand why autocomplete works sometimes and.
Other times it doesn't so that's a really important feature if you liked this video please comment below like this video and share it on your social.