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 ...
Read more

The Skilled Marketer Paradox: Do Good Marketers Need Jobs?

 Look around the world of work and at job board postings and you will see a ton of positions open for digital marketers. Companies have come to realize how effective and efficient digital media and content marketing can be in generating sales and brand recognition. Now that they see the need for marketing that works for them, they are trying their best to hire marketers to do that work for them . Often in the job postings there will be a requirement for previous experience. Few if any of the open positions will accept applicants with less than 2 years of previous experience doing marketing professionally. Lots of these positions even want 7-9 years experience. And they don't just want experience, they want proven results. They want marketers who can show them how they made an impact on the businesses they have worked for before. They want to see traffic and rankings growth. They want to see sales and conversions growth. But I see this as fundamentally unreasonable and impossible. I...
Read more