+ Reply to Thread
Results 1 to 8 of 8

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 Guru 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 2000/3/7/10/13/16
    Posts
    47,752

    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 Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,416

    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 2000/3/7/10/13/16
    Posts
    47,752

    Re: Names

    drwat please read post #3 and comply

  7. #7
    Forum Guru 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.

+ Reply to 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