How To Use Autocomplete With Data From A Different Column In Excel?

 For work I enter names in different columns. Sometimes the names are the same. It would save me a lot of time if Excel were to recognize that I was typing a name I have previously typed and suggest a finished string of text that I might want.

That feature is known as Autocomplete. If you are entering strings of characters (names, keywords, phrases, etc.) in every cell going down a column, then if you start to type the same string again, Excel will suggest to you something you've already entered in another cell.

This feature only works within a single column and all the the cells must be touching. That means that you cannot get Autocomplete suggestions based on text entered in another column, and you can't skip a row or leave a row blank on your way down a column, or else the Autocomplete stops working.

This is a really cool feature, but I can't make use of it because I want to have Autocomplete suggestions based on the text I've entered in EVERY other cell in the worksheet. I don't care if I entered the text in column A or column D, I want to see a suggestion if I previously entered that string of characters before.

I googled this issue to see if there was a setting I could tweak or a technique I could use to make Excel do what I wanted it to do. I read several forum posts, and it seems like the answer is that it can't be done. Excel strictly limits Autocomplete to the current column. 

The Solution I Came Up With

I enter so many names, and they are repeat names so often that I had to find a way to make Autocomplete work for me. The only thing I could come up with was to enter all of my data in a single continuous column. I can enter all of what I would normally enter in column A, then when I would normally move to column B data, I just continue on in column A. 

I can make a cell in column A that is titled "Column B", or whatever title makes sense for what I'm doing, and highlight that cell a different color so that it is obvious where the next column's data starts. Then I just continue on with column B data in column A.

Because all of the cells are in the same column and there are no blank cells, Autocomplete continues to work. Then when I am all done entering the data, I can just copy and paste my columns into their proper spots in the worksheet.

This workaround isn't perfect, but it can really save a lot of time if the alternative is typing out the same strings of characters over and over.

And if anyone that works for Microsoft on the Excel program reads this, it would be great if you could just make a setting which would allow Autocomplete to look for options in every cell in the worksheet/workbook and not just the current column.



Do you know a better way to do this than I've suggested here? Let me know about it in the comments!




Other Articles to Read:

Comments

Post a Comment

Popular posts from this blog

The Importance of Entrepreneurship to the Economy and Society

Image
An entrepreneur is a special kind of person.  The entrepreneur is a life/world/reality designer.  The entrepreneur takes what isn’t yet and makes it happen. Someone who is entrepreneurial or chooses the path of entrepreneurship will create the world that others live in.  Where most people must settle for the way things are and must pounce on the job opportunities they see in the world because those are the limit of possibilities for them, the entrepreneur will choose to create opportunities for themselves and others when they do not see the opportunities they wish for. Clearly, I think highly of entrepreneurs. Can Everyone Be An Entrepreneur? I think that there are some people who lack the skills and the mindset to ever be entrepreneurial. They will go through their entire life only taking what other people give to them or allow them the chance to work for. All of their life’s choices are simply what more spirited people have created for them. 
Read more

What Is the Purpose of a Blog Post?

Image
When people are new to blogging and they are trying to research what makes a good blog post or how to improve their blog’s performance measures through their content, a certain type of advice comes up almost every time. “Write the BEST content on your article’s topic.” “Make your article better than every other article on the web.” “Make your post the definitive, most detailed resource available.” “If someone finds your article, they shouldn’t need to look for any other information anywhere else because your post will be so complete that they will have learned all they need to learn about the subject.” This is a wildly intimidating goal to set for your content, and unfortunately, while the intention of setting a lofty goal is that you will end up achieving better results for it, what most often happens is that you will develop a fear of failure and that will prevent you from even starting. Which is the worst possible outcome. Even a failure that gets finishe
Read more

Google Chrome Touchpad Scroll Not Working SOLUTION

Image
I use and love Google Chrome . It is my favorite web browser and my default program for web browsing.  Unfortunately, I’ve had some trouble with it recently, and it has caused me such frustration that I considered switching to an alternative such as Mozilla Firefox .  I think I have found the solution to the problem, though! Description of the Problem I primarily use Chrome on my laptop, and I have become accustomed to using the tracking pad’s touchpad gestures. I use two fingers at once to scroll down pages.  I sometimes use other people’s laptops which don’t offer that gesture, and I quickly realize how much I like that feature.  On my own laptop, in Chrome specifically, I will sometimes lose the ability to two-finger scroll. I will be on a page, I will have two finger scrolled some ways down the page, and then suddenly it will stop working. The computer won’t be frozen, I will be able to click the arrows at the right-hand side to scroll the page, I will be
Read more