+ Reply to Thread
Results 1 to 13 of 13

Predictive population

  1. #1
    Forum Contributor
    Join Date
    10-18-2010
    Location
    Walla Walla, Washington
    MS-Off Ver
    Excel 2007
    Posts
    124

    Predictive population

    I have a list of players located on the sheet ' Players'. When I start to enter one of those players on the sheet 'Draft Board', I want the closest name to populate so it can quickly be entered. Is that possible in excel. I've tried a few different ways but nothing will work. At this point I'm not looking for direction but hope someone can help with a solution. Thank you for your help in advance. See attached.
    Attached Files Attached Files

  2. #2
    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,900

    Re: Predictive population

    Yes, this can be done. But you do need to use some VBA. Take a look at this and see if it is what you want. I have it set up for columns F & H. If it's what's needed, I can explain.... enable macros when you open it.

    DOUBLE click on any of the relevant cells and start typing...
    Attached Files Attached Files
    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

  3. #3
    Forum Contributor
    Join Date
    10-18-2010
    Location
    Walla Walla, Washington
    MS-Off Ver
    Excel 2007
    Posts
    124

    Re: Predictive population

    Glenn, thank you for the idea. I see there are list boxes that allow me to scroll through and select the name but that would be very tedious from a time management standpoint. With what you did, I noticed that when I entered a name that is already been typed once, it populated effectively. However, If I wanted to enter a name like Josh Gordon, CLE, it wouldn't self populate until I've already entered the name once. What I would like is for it to continue refining as I type more. Also, it would need to be able to autopopulate the first time it is entered on the sheet.

    If thats what it actually does, maybe its just incompatibility with my version of excel. Thoughts?

  4. #4
    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,900

    Re: Predictive population

    DOUBLE click the cells.

  5. #5
    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,900

    Re: Predictive population

    Sorry. Double click IN the cell.

  6. #6
    Forum Contributor
    Join Date
    10-18-2010
    Location
    Walla Walla, Washington
    MS-Off Ver
    Excel 2007
    Posts
    124

    Re: Predictive population

    Ahhh. That did it. Thank you.

  7. #7
    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,900

    Re: Predictive population

    When you do that, you'll sort of see a slight change in the cell as a slightly larger box opens, slightly more than obscuring the cell you clicked in. Start typing in it.

  8. #8
    Forum Contributor
    Join Date
    10-18-2010
    Location
    Walla Walla, Washington
    MS-Off Ver
    Excel 2007
    Posts
    124

    Re: Predictive population

    Would you be willing to do this across the sheet? Thank you again.

  9. #9
    Forum Contributor
    Join Date
    10-18-2010
    Location
    Walla Walla, Washington
    MS-Off Ver
    Excel 2007
    Posts
    124

    Re: Predictive population

    Actually, I did it. thank you.

  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,900

    Re: Predictive population

    I assume that it is what you need. i created a Named Range (CTRL F3 to view it) which will adjust automatically as players are added and/or subtracted. Right click on the Tab name/View Code and you'll see the VBA (copied from Debra Dalgleish http://www.contextures.com/xlDataVal11.html) that makes it run. I have extended it to cover the remainder of those columns.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    10-18-2010
    Location
    Walla Walla, Washington
    MS-Off Ver
    Excel 2007
    Posts
    124

    Re: Predictive population

    Is there a way to get rid of the initial drop down box so its not confusing?

  12. #12
    Forum Contributor
    Join Date
    10-18-2010
    Location
    Walla Walla, Washington
    MS-Off Ver
    Excel 2007
    Posts
    124

    Re: Predictive population

    That is magnificant! Thank you.

  13. #13
    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,900

    Re: Predictive population

    No. You can't get rid of it. Just include a message box to remind yourself to double click in the cell.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Predictive Formula Bar
    By techteam in forum Excel General
    Replies: 2
    Last Post: 08-14-2012, 06:09 AM
  2. Predictive Model Help
    By jbiker in forum Excel General
    Replies: 0
    Last Post: 07-10-2012, 08:09 PM
  3. Replies: 0
    Last Post: 07-10-2012, 08:37 AM
  4. Predictive Text
    By VisualBasic in forum Excel General
    Replies: 2
    Last Post: 10-31-2011, 04:24 AM
  5. Predictive Text
    By VisualBasic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2011, 03:18 AM
  6. Predictive Stock
    By pickle in forum Excel General
    Replies: 7
    Last Post: 05-07-2011, 05:29 PM

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