Closed Thread
Results 1 to 11 of 11

Auto complete Names

  1. #1
    Registered User
    Join Date
    03-12-2013
    Location
    kansas
    MS-Off Ver
    Excel 2007
    Posts
    2

    Auto complete Names

    I want to enter a name in a cell from a list of 70 names I have on another work sheet. By just typing the first few letters of the name into the cell I need it to complete the entry. I do not want to use a drop down box it takes too long to find the name. I need a formula to do this for my spread sheet. thanks
    Last edited by drwat; 03-13-2013 at 05:06 PM. Reason: Change Title

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Names

    drwat,

    Welcome to the forum!
    Attached is an example workbook based on the criteria you described.
    The workbook has two worksheets, the first one is 'Sheet1' and that is where you can type in and have the auto-complete. The second one is 'Names' and that worksheet contains the list of names.
    After I entered the list of names (which I got from http://names.mongabay.com/) into sheet 'Names', I created a dynamic named range which is defined with this formula:
    Please Login or Register  to view this content.


    Then in sheet 'Sheet1' cell A2 and copied down to A101 (100 total cells) is this formula:
    Please Login or Register  to view this content.

    I then hid rows 2:101 on 'Sheet1'. Now when you start typing in cell A102, you'll see it attempt to auto-complete the name entry. For example, type in ba and it will suggest Barbara Nelson, or type in ron and it will suggest "Ronald Clark".
    Once it is suggesting a name, just press Enter and Excel will auto-complete and move to the next cell.

    Does that work for you?
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Names

    Hi drwat, and welcome to the forum

    We would love to help you with your question, but 1st, please rename your thread to something more meaningful, that actually describes your problem. Many members will look at a thread title, and if it is of interest to them, or falls within their area of expertise, they might only open those threads. also, searching for ""help"" will not turn up too many results that will benefit you, sorry.

    Look at it this way...if you typed that title into google, what would you expect to get back?
    To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Names

    There's an add-in to do this at https://www.box.com/shared/ono8ni9xvr, an example workbook at https://www.box.com/shared/e8dbbple34, and a ReadMe at https://www.box.com/shared/4qxybepxxr

    .
    Last edited by Paul; 03-13-2013 at 06:29 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    03-12-2013
    Location
    kansas
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Names

    Hi, I am having a little trouble everything looks good I just do not see where I put the dynamic named range formula.[code] =Names!$A$2:INDEX(Names!$A:$A,MAX(2,COUNTA(Names!$A:$A))) what do I do with that formula? I understand how sheet1 works.
    Thanks

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Names

    drwat please read post #3 and comply

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Auto complete Names

    drwat,

    THank you for updating your thread title. As for where to enter the dynamic named range formula, in Excel 2007 go to the Formulas Tab and click Name Manager

  8. #8
    Registered User
    Join Date
    08-30-2018
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    1

    Re: Auto complete Names

    Thanks for the solution offered above. I have replicated it in a spreadsheet, much appreciated.

  9. #9
    Registered User
    Join Date
    09-03-2019
    Location
    Paris
    MS-Off Ver
    2017
    Posts
    1

    Re: Auto complete Names

    It seems very instructive, but I can't have access to the xls file, what is the next step?
    Last edited by skoame; 09-03-2019 at 05:55 AM.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Auto complete Names

    Please don't hijack an old thread. Start your own, new, thread. (See rule 4 that YOU agreed to adhere to only just today!! Also explain more clearly what you mean by your VERY brief description.

    I do hope you do not want to bypass security settings on a file.... (see Rule 7).
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  11. #11
    Registered User
    Join Date
    05-11-2021
    Location
    Texas
    MS-Off Ver
    Windows 10
    Posts
    6

    Re: Auto complete Names

    How do I gain access to the excel file that tigeravatar posted?

    Any help would be appreciated!!

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1