Text Autocomplete in Excel Based on Locally Defined Dictionary

Question

Is there a way to implement an auto-completion/validation feature in Excel that checks text against a locally defined dictionary?

We have around 100 known terms for which Excel should suggest an auto-completion. In addition, we have about 100 additional exclusion items (mostly acronyms and abbreviations) we want the program to auto-correct. The catch is that this custom dictionary must be consistent for all team members.

Our requirements are:

• The autocomplete/suggestion feature must inform the users if/when a name doesn't match against a known value, but the user should still be able to over-ride based on the suggestion.
  • The dictionary must also be scoped to the workbook itself and not tied to a single machine or user account.
  • We may use VBA only if there are no other viable solutions.

Here’s what we’ve looked at so far:

1. Drop-down lists in Excel's Data Validation feature set do not autocomplete the phrase. Since the drop down list is fairly large, we won't be able to use this feature UNLESS it incorporates suggestions or autocompletions functionalities.

2. We've also looked at implementing custom dictionaries through Office/Excel, but these appear to be scoped to a single machine and/or user account. We need this solution to work for multiple individuals. This won't work UNLESS we can use custom dictionaries that are defined in the workbook's configuration parameters, tables, named ranges, or other data sources that are scoped locally to the workbook itself.

3. Form and ActiveX controls (i.e., list boxes) are not viable options at this time UNLESS they can be populated with a control source without using VBA code.