Closed Thread
Results 1 to 20 of 20

Auto Complete in a Data Validation cell technique (feedback wanted)

  1. #1
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Auto Complete in a Data Validation cell technique (feedback wanted)

    Here is my idea presented for Auto Complete Assist in a Data Validation Drop Down Cell.

    Auto Complete Assist

    It's not a true autocomplete, but it was a fun technique to layout and I'd like some feedback to iron out the wrinkles you may find.

    Thanks in advance for the feedback.


    (also called for feedback from here: http://www.mrexcel.com/forum/showthr...23#post2247523)
    Last edited by JBeaucaire; 12-27-2019 at 10:49 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    Edit: Deleted. I'm a moron.
    Last edited by darkyam; 03-15-2010 at 09:34 PM.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    That works for one cell, but for a form where I'm going to have a column of cells using this same drop down, that becomes impractical I think. Your method would require two helper columns that stretched the entire data set PER CELL that was going to use the lists. I would only need to go down 130 rows and it can no longer be done in Excel 2003...you run out of columns...and your sheet would be huge by then!

    Better to have the lists laid out one time in multiple columns/ranges to start with and then an infinite number of cells can use them.

    Keep it coming...!
    Last edited by JBeaucaire; 03-15-2010 at 09:50 PM.

  4. #4
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    JB,

    I think this is a great technique and, frankly, don't see how the lists can be further refined given the wide disparity of possible names.

    It isn't intuitive to select from twice from separate lists the same drop down, so possibly the first selection in each name list might be "choose a name". You included notes to tell us to do this but, obviously, such a note as presented would not be shown in the finished product.

    I like this and, shamelessly, will "borrow" it. Thanks for sharing.

    Palmetto
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    Quote Originally Posted by Palmetto View Post
    It isn't intuitive to select from twice from separate lists the same drop down
    Good point, in application, yes I would have to include some training to aid them in getting familiar with the technique.

    I like this and, shamelessly, will "borrow" it. Thanks for sharing.
    That's the goal! Go for it. When you do, please come back and feedback how it actually pans out in your real application. I can fathom how only two cells could be used to choose from a wide variety of "options" for customizing a product, like a T-Shirt, you know? Would like to see this actually work out for use.

    Jerry

  6. #6
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    Haven't had time to think about this in a while, but I was determined to find a way to do it. I found a formula that can work with data validation, so no other cells are needed, yet still matches based on multiple first letters, making it useful if you have an exceedingly long list and eliminating the need to break up the original list. It can be copied over, down, etc. I don't know whether it is more or less efficient than yours from a calculation speed standpoint, but it doesn't need Indirect to work. If you type in a letter beyond the list, it simply returns the last available entry as the only one in the dropdown.

    You may have to test it, but in my limited testing, it seems to work fine, assuming your list is in alphabetical order.

    *Edit: simplified formula slightly.
    Attached Files Attached Files
    Last edited by darkyam; 03-22-2010 at 12:02 AM.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    That's pretty slick. It's an interesting side-effect that selecting any name gives you a drop down with that name and the next one. I can think of a couple of uses for that!

    Want to play with it to see if you can get it to maintain my original design that after any name is chosen, the drop down in that cell displays all the names starting with same first letter? Choosing Cherise would still show all "C" names...?

  8. #8
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    That's an unintended side effect. I didn't think most people would bother typing in the entire name. It's due to the first index/match adding 1 to the match it finds. It can be corrected by changing to:
    Please Login or Register  to view this content.
    Getting it to show all C names after choosing a C name can be done with
    Please Login or Register  to view this content.

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    Hmm, still not getting the intended results. When I use the second code, selecting the same cell again simply presents the entire list again.

    The goal is to maintain the 2nd use feature of my original column B on Sheet2...second use shows all the names of that letter group.

    The lists being hidden on a second sheet I thought was a clear prerequisite, too. Sorry if it wasn't. The resulting formulas used would need to be on Sheet2. That's why I used named ranges in my setup, to make it simple to maintain.

  10. #10
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    It was clear where you wanted it, but I was admittedly being a little lazy by putting it in Lists so I wouldn't have to go back and forth constantly to check it when there were errors. Probably should have made a dynamic named range and replaced $j:$j in the formula with it.

    I don't know why you don't seem to be able to get it working. It works fine for me. The only thing I can think of is an issue between 2003 and 2007. When I pick Cherise on 2007, it still gives me all C names. I tested that before I posted the first time.
    Attached Files Attached Files

  11. #11
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    Also, I'm not entirely certain why you'd want it to give you all C names when one of them is selected. If you had a list of, say, 5,000 names, doing it this way would give you a few hundred names to pick from. With my other formula, you'd be able to just type in the first few letters and knock that down to only a handful of names. The only real benefit I can think of is if the user often accidentally clicks a name other than the intended one and has to select again. With a short list, like the one we're dealing with, I'll grant that it doesn't matter either way and it is kind of nice to see the whole list for each letter, but with a larger one, it might start to get unwieldy.

    Here's an idea: what if there was a cell somewhere that had a number in it and that number went into the Left() formulas as the number of characters? There would have to be an error handler if the cell contained fewer than that number of characters (preferably a way to get it automatically to said number of characters), but this would serve as a precision guide. With a short list, it could be set to 1. With a very large list, it could be set to 3 or 4, so that the first several letters have to match, thus giving much the same effect, but keeping the list short in nearly all instances.

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    Looking at your new sheet, it still behaves a little oddly.

    1) Choosing Cherise does present the "C" names when you look again, but typing in Andy doesn't. There seems to be no "A" list of names.

    2) There is no default list anymore, perhaps not much of an issue.

    My original technique at least "moved you through" the process of presenting subset choices. Also it did that, too, even if the cell was blank or had a space in it...

    ========
    As for helper cells, I wouldn't want that, but for your own development, that might be a possibility. I'm still looking to maintain the single-cell technique with all the features of the original, for now.

    1) Empty or spaces in a cell presents a sub-list of name "groups"
    2) After making a choice, the list shows all names from that group
    3) After making a final name choice, the list keeps showing that group until cell is cleared.
    Last edited by JBeaucaire; 03-22-2010 at 11:27 AM.

  13. #13
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    It's starting to sound like we're never going to see eye-to-eye on this, which is fine. I found the issue to the no A-list thing and the no main list thing. They were the same issue, and that's that I was referring to $J$2 in the formula. I named that cell and replaced the reference with the name.

    As for helper cells, there would only be one cell that would apply to the whole workbook. If you want it to show hundreds of names for a giant list, that's fine. We just have a difference of opinion on the best way to do this, apparently.
    Attached Files Attached Files

  14. #14
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    Heh..."apparently"...funny how one word can color a whole phrase.

    Anyway, I was able to take your formula and tweak it back to my original criteria. So this does improve the implementation for an alphabetic listing. It allows us to put it back into a single column and eliminate the need for all the other named range lists.

    Now we're down to 2...the list of "alpha-categories" and the main listing of names. The final formula I used is:

    =IF(TRIM(B2)="",AlphaList,INDEX(NameList,IF(ISERROR(MATCH(LEFT(B2)&"*",NameList,0)),MATCH("*",NameList),MATCH(LEFT(B2)&"*",NameList,0))):INDEX(NameList,MATCH(LEFT(B2)&"zzz",NameList)))
    Attached Files Attached Files

  15. #15
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    I was able to get the multi-category version to work even better.

    Download: FILE PAGE
    (the file to download is called: AutoCompleteDataValidation(SortOf)3.xls)

    This works even better, built off of the work done with the single column v.2 previously, this gives us the same technique in multiple lists that do not have to be alphabetic.

    Have a look.
    Last edited by JBeaucaire; 12-27-2019 at 10:51 PM.

  16. #16
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    There are now 3 separate pages showing 3 different implementations of this technique:

    Same Cell Cascading Dependent Drop Down

    Phew...
    Last edited by JBeaucaire; 12-27-2019 at 10:52 PM.

  17. #17
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    We're up too 4 different pages of this technique, but I've updated a couple of these to now be xl07+ compatible. xl07+ no longer allow array/union formulas in data validation, but the technique still holds good moving the formula into a Named Formula instead.

    Updated Data Validation Sub List Techniques
    Last edited by JBeaucaire; 12-27-2019 at 10:52 PM.

  18. #18
    Registered User
    Join Date
    03-25-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    Hi Jerry,
    You sort of seem to be on the path that I'm desperately trying to find, but all of the examples I have seen don't solve my problem. I have a MASSIVE (1500 rows) list of Customers with address,name,title,phone,email etc, and all I want to do is type the first one or two letters of the company name into a box and have the list sort by what i type in. Sort of like in Windows Explorer detail view, you can click any file and hit a letter and the list will jump to all files beginning with that letter. Seems like a simple thing to do, but it's turning into a major exercise in patience. Can Excel even DO that? Or can you recommend another way? Thanks for all the time that you have obviously spent on this topic, some very cool sheets as well.
    Regards,
    Colin

  19. #19
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    You're talking about COMBO BOX and the autocomplete function built into it. This thread is purely for Data Validation, which does not have true autocomplete as an option. This whole thread is about a middling workaround, there's no way to turn a DV list into a true autocomplete. That's what combo boxes are for.

  20. #20
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Auto Complete in a Data Validation cell technique (feedback wanted)

    Another fully presented method, Dynamic Cascading Drop Downs that put all the work into the data validation formula so there is no Indirect() issue:

    http://www.excelforum.com/excel-tips...t-problem.html
    Last edited by JBeaucaire; 12-27-2019 at 10:53 PM.

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