[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 674
  • Last Modified:

Excel to suggest name when you start typing

I have a list of names maybe 40 names long.
I don't want a drop down to choose the name I am looking for, but I want to start typing the name, and  the cell will be smart and suggest the rest of the name based on the first couple letters put in.

Example........

Chris is the name I want in my cell.....When I put in just the letter "C" it will start to suggest  Christopher or Christian ect.
Is this possible?
Thanks
0
chris pike
Asked:
chris pike
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Not in an excel cell. Maybe in a userform.
0
 
chris pikeAuthor Commented:
Is there anything close? It doesnt have to give the suggestion but maybe jump to that part in the list?
0
 
Rob HensonIT & Database AssistantCommented:
There is Autocomplete.

This occurs when the entries in the cells above have the similar entries to what you start to type in the cell. However, it only occurs when there is one option that it can suggest.

For example, if you had in previous cells in the same column:

Christopher Columbus
Chris Pike
Chris Biggins

If you started typing Chris it wouldn't make any suggestions until it could give a unique suggestion, ie if you typed Chris P it would autocomplete the rest of Pike. If you typed Christo it would autosuggest Christopher Columbus.

There is also the "Pick from list" right click option.

Right click on a cell one below the bottom of a list there is an option to pick from Dropdown List. This creates a list of entries from cells above. This doesn't have to be a predetermined list like Data Validation dropdown list but will show a list of previous entries in that column.

Thanks
Rob H
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
chris pikeAuthor Commented:
Thanks Rob,
That could work , but I need to determine where the cell is located to enter the name.
My cell will be in a different sheet. Then I will do a bunch of vlookups to fill in a table.
If I was better at pivot tables it would be much easier.
0
 
xtermieCommented:
You can get a list of all possible names (first names I suppose) and put them in another sheet and create a validation rule, that can be used as a list (which you don't want, I get it) but also will suggest when typing.
0
 
Rob HensonIT & Database AssistantCommented:
Can you upload a sample file with your expectations that we can take a look at?

Thanks
Rob H
0
 
ProfessorJimJamCommented:
Hi Chris,

one easy option is to use the ActiveX Combo box.

please see attached workbook.

try to type inside the blue box, for example if you type J you will get auto suggestion Joseph Porter  then if you type another "a" like Ja then you will get auto suggestion Jackson Thompson and if you add m like becoming Jam then you get James Spencer

it is easy to create.   you create a name list then insert a active X combo box from the delveoper tab. then click on deisgin veiew and click on the combo box and then click on properties and then on the properties there is something called fillrange and put the named range there.  see the screenshot. 2016-08-30-16_58_07-Microsoft-Excel-.pngEE.xlsx
0
 
Rob HensonIT & Database AssistantCommented:
Chris, can you expand on your aversion to having a dropdown box to select from?

I can understand your aversion if you use a list which has repeated values and not sorted alphabetically. If that is the case it is possible to create an alphabetic list of possible entries on which to source the dropdown. One way of doing this is with a Pivot Table and just show the Name field in the Pivot, thus creating a list of available names; maybe this is what you were referring to when mentioning Pivot Tables in your earlier comment.
0
 
Ejgil HedegaardCommented:
Hi Chris

Here is a solution I made a while ago for another project.
Check if that can be adapted to what you want.
I put in the name list (Table sheet) from ProfessorJimJam.
It is automatically sorted when new values are added or removed.
It is data validation as you type.
Select a cell in column E (provider range), and a box is shown on top of the cell.
DataValidationAsYouType.xlsm
1
 
chris pikeAuthor Commented:
@Jim, thanks for the option, this is very similar to a Consolidate-List drop down.
Was hoping to get something that would finish what I am typing or suggest based on the first few characters I start to enter.

@Rob, thanks, I need one cell that I enter the name of a person, that person is in a table on a second sheet.
Then I will do Vlookup to to fill in other information next to the name in the new table.

Thanks guys. Hope that make sense.
Chris
0
 
chris pikeAuthor Commented:
@Ejil, very fancy drop down, however it is still a drop down.
I would love to just start typing the name of the person, and it would complete it for me.
Thanks though, yours looks cool.
Chris
0
 
Martin LissRetired ProgrammerCommented:
Given a list with just two names - "Martha" and "Martin", if you type "M"would you want "Martha" to show up?
0
 
chris pikeAuthor Commented:
@Martin, Just would like a little help finding the name somehow. Maybe skip to that part of the drop down or show both. Or something like google when you start typing something in and it suggests for you.
Does that make sense?
Thanks
Chris
0
 
chris pikeAuthor Commented:
Wow, sorry Ejgil , I had a second look at your solution, it is in fact what I am looking for.
I didn't realize,
Thank you very much it works great.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now