Validation to Give Suggestions on Error

[breynolds0431]

I have a workbook which requires the user to typein a county name. There is then a vlookup that returns the urban/rural designation based on that county name. I was wondering if there was a way, when the county name was entered incorrectly and results in an error, for a form to pop-up with suggestions from the vlookup array to show and let the user select a county with a name close to what the user entered.

Thank you for looking at my question.

 

[nardagus]

Hello,

From your post I gather that there is list of countries somewhere your your workbook?
Maybe Data Validation as a list would help you with it? Users would have a list of countries to choose from, eliminating a risk for mistype?

1) Select desired cell (or cell range) you want to set data validation.
2) Go to Data Validation.
3) In a first field select List
4) In a Source field select cell range containing a list of countries
5) Leave other options as they are
6) Click OK.

In a cell you will have small arrow button. After you click it you will see a list of countries. Just scroll through a list to find a desired country and click on it. This feature will also check if a user tries to type something which isn't on that list. If yes then excel will popup a warning and block saving a new value in this cell.

Nard

 

[breynolds0431]

Hi Nard -

Thank you for the suggestion. At first I wanted to steer clear of drop-down validations due to the county listing including all US counties. Using the drop-down scroll would become tedious with the massive county listing. However, I noticed that you could use the list validation without including the in-cell drop-down. I think this might be the best route as the user can type the county directly in without worrying about case-sensitivity. I thought I remembered drop-downs or list validations would auto-complete as the user typed in the cell. But that doesn't seem to be the case, unless you are aware of anything else.

Again, thank you for the response.

 

[Peter_SSs]

If you have a large list and you would like the list to narrow down as you type, have a look here.

 

[Akuini]

Using the drop-down scroll would become tedious with the massive county listing

How many item in the list are we talking about? more than 2K?
I suggest to use combobox (put in the sheet not in a form). And you can create a macro to make the combobox behave like this:
when you type "ham" it will show Hamilton, Hampton, Hammond etc, so you can pick one of them.

 

[breynolds0431]

Peter and Akuini - thank you both. The combobox works great.