Forum Discussion

Anwesh Gangula's avatar
Anwesh Gangula
Iron Contributor
Jul 10, 2018

Auto Complete for Data validation list

Is there a way for excel to Auto-Complete/Suggest the name in a cell with data validation list.  without using VBA/Plugins. I would like to use this in an Excel file that I need to upload in SharePoint and share with my team.

Excel Desktop
Formulas & Functions

2 Replies

Sort By
  • Man Fai Chan's avatar
    Man Fai Chan
    Iron Contributor
    Jul 10, 2018

    I am not sure if there is such a function. 

     

    I suggest to prepare a lookup table and then use the vlookup to provide the hint/auto-complete.

    • Anwesh Gangula's avatar
      Anwesh Gangula
      Iron Contributor
      Jul 10, 2018

      I found a really good discussion in Stack Overflow. 

      They had provided a workaround using a named range with offset function as a Data validation.

       

      =OFFSET(Sheet2!$A$1,MATCH(Sheet1!$A$1&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))

       

      But I was not able to figure out the following from the post

      • "The list will go till the end of your data. If you want to be more precise (keep in the list only the matching elements), you can change the COUNTA with a SUMPRODUCT that will calculate the number of matching elements"

      I tried using the SumProduct formula instead of COUNTA but to didn't work.

Resources