Forum Discussion
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 SharePoi...
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.
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.