Forum Discussion
Predictive text in an Excel drop down list
adversi Hi thanks for the reply. It is a roster and Kms tracking spreadsheet. We are about to expand and Head office has a new national rostering system coming hopefully in a few months in the meantime we use this. We currently have 35 staff and 50 odd participants. So with the drop down boxes containing 35 staff and growing, scrolling down is slow and clumsy. Predictive text would really help...
kind regards Cambosity
Thank you for providing more detail on your project. After reviewing it, unfortunately I don't see the VBA dropdown being beneficial in this case due to the number of dropdowns in the workbook. My advice would be to sort and list all referenced dropdown lists in a separate tab to make the dropdowns cleaner. The final list should have all blanks removed and be sorted alphabetically.
New dropdown reference
New dropdown format
There is a reason for the blanks. They correlate with other calculations that can be dragged. But searching the internet found this formula.
=FILTER(C2:C32,ISNUMBER(SEARCH('RWk43'!E4,C2:C32)),"Not Found")
This I place in D3 of the Staff tab and then change the data validation source to just Staff!D3# ...
When in the roster I can now type a couple of letters and hit the drop down and hey presto ! there are the names related to those letters. Not predictive but real close 🙂
Thankyou for your input it is greatly appreciated 🙂