Results 1 to 1 of 1

Thread: Searchable DropDown list in Data Validation without using VBA

  1. #1
    Neophyte Len88's Avatar
    Join Date
    Aug 2021
    Excel Version

    Searchable DropDown list in Data Validation without using VBA

    Register for a FREE account, and/
    or Log in to avoid these ads!


    As my excel version does not have filter, sort, unique functions I have to use other set of formulas to search the drop down list in data validation.
    A range of data is created with Code and BP name in column A & B under Master sheet where "Master" name range is set on column B and Data validation list is set without error alert at cell B3 and below under Entry sheet per sample file attached.

    Below set of formulas copied from google search and modified :-
    1) Master name range "OFFSET(Master!$B$2,,,COUNTA(Master!$B:$B)-1)"
    2) Data Validation List "OFFSET(Master!$B$2,MATCH($B3&"*",Master,0)-1,,COUNTIF(Master,$B3&"*"),)"

    When new BP name & code are added to the list of Master sheet, the list will be updated and sorted automatically in data validation list.
    If there are more than 100 names to search in drop down list, it would be more efficient to use dynamic search in drop down list.
    However, when I try to do dynamic search by "Trad" or "Co" in cell B3 in Entry sheet, the drop down list show nothing ??

    Did I miss out anything in my formulas ?

    My objective is to make every row of data entry as dynamic search in drop down list data validation such as filtering search without VBA and it will be refresh the drop down list for next row of entry.

    Appreciate if anyone can help to solve the above problem and thanks in advance.

    Attached Files Attached Files

Posting Permissions

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