Searchable drop down lists - 7000+ rows

  • Hi


    I am presently have combo boxes with searchable drop down lists using excel formulas but the process is very slow


    I am hoping that VBA can speed up the process but not sure how to do so


    this is what I presently have


    Column R: =--ISNUMBER(SEARCH(Sheet3!$I$10,Sheet14!A5172))
    Column S: =IF(R2=1,COUNTIF($R$2:$R2,1),"")
    Column T: =IFERROR(INDEX($A$2:$A$7000,MATCH(ROWS($S$2:$S2),$S$2:$S$7000,0)),"")


    I also created a file name in the Comb Box properties / ListFillRange - managers : =Sheet14!$T$2:INDEX(Sheet14!$T$2:$T$7000,COUNTIF(Sheet14!$T$2:$T$7000,"?*"))


    Is there VBA coding that would speed up the process and keep the searchable drop down list?


    thank you
    Dan

  • Here is the portion that is completed by the employee (Sheet 3) [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 896"]

    [tr]


    [TD="colspan: 6"]Employee Name (First name & Last Name):*[/TD]

    [td][/td]


    [td][/td]


    [TD="colspan: 6"]Delegated Manager Name (First Name and Last Name):*[/TD]

    [/tr]


    [tr]


    [TD="colspan: 6, align: left"] [TABLE="cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="colspan: 6"] [/TD]

    [/tr]


    [/TABLE]
    [/TD]

    [td][/td]


    [td][/td]


    [TD="colspan: 6, align: left"] [TABLE="cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="colspan: 6"] [/TD]

    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [tr]


    [TD="colspan: 2"]Home Address:*[/TD]
    [TD="colspan: 4"]#N/A[/TD]

    [td][/td]


    [td][/td]


    [TD="colspan: 2"]Foor # / Room #: [/TD]
    [TD="colspan: 4"]#N/A[/TD]

    [/tr]


    [tr]


    [TD="colspan: 2"]City:*[/TD]
    [TD="colspan: 4"]#N/A[/TD]

    [td][/td]


    [td][/td]


    [TD="colspan: 2"]Physical Address:*[/TD]
    [TD="colspan: 4"]#N/A[/TD]

    [/tr]


    [tr]


    [TD="colspan: 2"]Postal Code:*[/TD]
    [TD="colspan: 4"]#N/A[/TD]

    [td][/td]


    [td][/td]


    [TD="colspan: 2"]City:*[/TD]
    [TD="colspan: 4"]#N/A[/TD]

    [/tr]


    [tr]


    [TD="colspan: 2"]P.O. Box / Apt #: [/TD]
    [TD="colspan: 2"]#N/A[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="colspan: 2"]Province:*[/TD]
    [TD="colspan: 4"]#N/A[/TD]

    [/tr]


    [tr]


    [TD="colspan: 2"]Province:*[/TD]
    [TD="colspan: 2"]#N/A[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="colspan: 2"]Postal Code:*[/TD]
    [TD="colspan: 2"]#N/A[/TD]

    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    they type in the name in the combo box and a drop down of options appear (like google)....once a match is found, the information is populated in the cells that presently have #N/A


    Data is found on Sheet 14 [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 1344"]

    [tr]


    [td]

    Sandra Ginn

    [/td]


    [td]

    Sandra

    [/td]


    [td]

    Ginn

    [/td]


    [td]

    Corporate Classification Project Officer

    [/td]


    [td]

    xxxx

    [/td]


    [td]

    xxxx

    [/td]


    [td]

    xxxx

    [/td]


    [TD="align: right"]59[/TD]

    [td]

    xx

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Ottawa

    [/td]


    [td]

    Ontario

    [/td]


    [td]

    K1A 0Y9

    [/td]


    [td][/td]


    [TD="align: right"]1[/TD]
    [TD="align: right"]2[/TD]
    [TD="colspan: 2"]Stan Farber[/TD]

    [/tr]


    [tr]


    [td]

    Stephanie de Wilde

    [/td]


    [td]

    Stephanie

    [/td]


    [td]

    de Wilde

    [/td]


    [td]

    Administrative Assistant - FSEP/HACCP

    [/td]


    [td]

    xxxx

    [/td]


    [td]

    xxxx

    [/td]


    [td]

    xxxx

    [/td]


    [TD="align: right"]174[/TD]

    [td]

    xx

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Guelph

    [/td]


    [td]

    Ontario

    [/td]


    [td]

    N1G 4S9

    [/td]


    [td][/td]


    [TD="align: right"]1[/TD]
    [TD="align: right"]1[/TD]
    [TD="colspan: 2"]Stephanie de Wilde[/TD]

    [/tr]


    [tr]


    [td]

    Stan Farber

    [/td]


    [td]

    Stan

    [/td]


    [td]

    Farber

    [/td]


    [td]

    Senior Counsel

    [/td]


    [td]

    xxxx

    [/td]


    [td]

    xxxx

    [/td]


    [td]

    xxxx

    [/td]


    [TD="align: right"]1400[/TD]

    [td]

    xx

    [/td]


    [td]

    xx

    [/td]


    [td]

    xx

    [/td]


    [td]

    xx

    [/td]


    [td][/td]


    [td]

    OTTAWA

    [/td]


    [td]

    Ontario

    [/td]


    [td]

    K1A 0Y9

    [/td]


    [td][/td]


    [TD="align: right"]1[/TD]
    [TD="align: right"]2[/TD]
    [TD="colspan: 2"]Stan Farber[/TD]

    [/tr]


    [/TABLE]

    last 3 columns are results from the excel formulas


    let me know if this helps


    Thanks
    Dan


  • Well ... Dynamic Named Ranges are NOT related to VBA ...


    For a good explanation, take a look at Debra's site:


    https://contexturesblog.com/ar…list-grows-automatically/


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)