Solved

Excel to suggest name when you start typing

Posted on 2016-08-30
14
58 Views
Last Modified: 2016-08-31
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
Comment
Question by:chris pike
14 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41776289
Not in an excel cell. Maybe in a userform.
0
 

Author Comment

by:chris pike
ID: 41776290
Is there anything close? It doesnt have to give the suggestion but maybe jump to that part in the list?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41776320
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
 

Author Comment

by:chris pike
ID: 41776343
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
 
LVL 17

Expert Comment

by:xtermie
ID: 41776358
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41776446
Can you upload a sample file with your expectations that we can take a look at?

Thanks
Rob H
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41776473
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 41776491
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
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41776862
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
0
 

Author Comment

by:chris pike
ID: 41777210
@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
 

Author Comment

by:chris pike
ID: 41777218
@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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41777366
Given a list with just two names - "Martha" and "Martin", if you type "M"would you want "Martha" to show up?
0
 

Author Comment

by:chris pike
ID: 41778045
@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
 

Author Closing Comment

by:chris pike
ID: 41778100
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now