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

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

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

Can Blogger Blogspot Blogs Rank In Google?

 I am a blogger. I want to write articles about things I care about, have them crawled, indexed, and ranked by search engines, and have people organically find and read my content. I am not writing only for me (although I do enjoy the process of blogging and writing my ideas out), I am writing to attract and inform/entertain an audience. If my content does not rank in the search engines, then nobody will find my content, and there is much less of an incentive for me to be blogging. From Google's perspective (and the other search engines), it seems that they want to show users the best possible content which will satisfy their queries. They should not have any biases for or against any sort of content. So long as a piece of content is valuable to readers, it should show up in the results. That is just good business for them. With the millions of blog posts being published every day , it is very difficult for a company like Google to determine which articles are the best quality and
Read more