Results 1 to 3 of 3

Thread: Way to use type ahead in a validation list?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2011
    Posts
    296
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Way to use type ahead in a validation list?

    Hello,

    I use a lot of lists in Data Validation. It gets tedious to bring up the list and scroll through until I find the one I want. Is there a way to use type ahead to select the choice without displaying the whole list?

    Thanks,

    George Teachman

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Way to use type ahead in a validation list?

    Maybe someone here can help you to set up a searchable data validation. Well, if you google 'searchable data validation' you'll find plenty example.
    But just an alternative idea, you may want to use a searchable combobox instead, you can find an example in this thread:
    https://www.mrexcel.com/forum/excel-...selection.html
    Last edited by Akuini; Aug 15th, 2019 at 11:19 AM.

  3. #3
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,524
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Way to use type ahead in a validation list?

    This video show how to type in part of a name and have the list update. It used the new dynamic arrays

    https://youtu.be/Z-h2UER3b_0


    If you do not have the new dynamic arrays then you will can use array formulas but you will have blank lines in the drop down.

    NOTE: when you setup DV you will need to unclick the "Show error alert after invalid data is entered" under error alert tab so that the list can update.

    A B C D E F G
    1 Ann name6
    2 dv----> na Beth name7
    3 Mike name8
    4 ben name9
    5 Becky name10
    6 name6 name11
    7 name7 name12
    8 name8
    9 name9
    10 name10
    11 name11
    12 name12
    Sheet2

    Array Formulas
    Cell Formula
    G1 {=IF(ROWS(G$1:G1)>SUM((ISNUMBER(SEARCH($B$2,$E$1:$E$12))+0)),"",INDEX($E$1:$E$12,SMALL(IF(ISNUMBER(SEARCH($B$2,$E$1:$E$12)),ROW($E$1:$E$12)-1+ROW($E$1)),ROWS(G$1:G1))))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Last edited by Scott T; Aug 15th, 2019 at 11:21 AM.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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