Auto Complete Data Validation List

**joebell** · 11-03-2012, 11:38 AM

Hi Everyone,

I searched the forum for possible answers to this question, but I can't seem to find anything on this exactly. I am trying to create a drop down data validation list that works also as an auto complete. Each month I merge several individuals reports containing names of regular customers and sales personnel. The individuals who create the data in these spreadsheets add names that do not belong or often misspell names which makes sorting difficult. Creating a data valadation list solves that issue, but I hear complaints of it taking too much time and effort to scroll down through a drop down list of numerous customers or sales people. The lists of customers or sales reps can be very long so I understand their concerns.

Is there a way to use these drop down lists in conjunction with an auto complete? I'd like for them to start typing the first several letters of the customer or sales rep until the correct name shows up, then they can easily select it by hiting the enter key. I created the data validation list above the column that is used and then I hid the rows. That works fine until a row is skipped, then the auto complete does not work. I'm sure you know what I mean. I attached a very simple, yet accurate sample of what I am currently doing. You see that I hid rows 1-9 which contain the data validation lists for columns B and C.

Any thoughts or any work arounds for this? Thanks!

**BB1972** · 11-03-2012, 03:12 PM

Hey joebell,

I believe I have a workaround for you. As you already have your lists in the same COLUMNS that will be used for the "Sales Rep" & "Company" input, you don't actually need to use a validation rule on cells B10:C40. I realise you might want to, with good reason, but, if you bear with me.... How about "forcing" people to use the next empty cell, so that they don't skip a row?

You can use custom data validation to prevent input in a cell if the cell above it (or any other one of your choosing) is empty.

I was trying to figure out figure out how to apply 2 validation rules to the same cell, but I'm not sure if that can be done. But then I realised that you may not need the 2 rules as your lists are above the cells that data will be entered in, so people just have to start typing the first few characters of a name, then press enter once the name shows up.

Have a look at the attached, and you'll see that the only cell you can make an entry in in Column B is B12, which is the first emtpy cell in that column. The custom validation in cell B12:

Please Login or Register  to view this content.

basically puts a condition on B12 that B11 must not be blank in order to have input in B12. This has been copied down to B40.

Let me know if this helps.

**Alf** · 11-03-2012, 03:44 PM

Or perhaps something like this could be of use?

On the "Data" sheet you just adds the company names and the sales representatives. As these ranges are dynamic you can add or delete company / sales rep. The macro will sett the proper range and sort them as well.

Clicking the macro buttons on sheet "Result" will bring up a userform where you start filling in name.

Alf

**HSV** · 11-03-2012, 08:21 PM

Only for .xls extensie.
It works in .xlsx extensie too, only after save, the formule don't work anymore (activate formula will do).
Type at least one letter in the validation list, then klick the validationarrow.

**johnny1234** · 06-03-2014, 01:20 PM

First of all, thanks HSV for the excel sample. That was exactly what I was looking for.
I would like to use your solution in a .xlsm file and was facing the issue that you explained in the .xlsx file.
Could you explain a little further what "activate formula will do" means?

Thank you!

**johnny1234** · 06-04-2014, 10:49 AM

Originally Posted by HSV

Only for .xls extensie.
It works in .xlsx extensie too, only after save, the formule don't work anymore (activate formula will do).
Type at least one letter in the validation list, then klick the validationarrow.

First of all, thanks HSV for the excel sample. That was exactly what I was looking for.
I would like to use your solution in a .xlsm file and was facing the issue that you explained in the .xlsx file.
Could you explain a little further what "activate formula will do" means?

Thank you!

**ABlevins** · 07-24-2014, 12:34 AM

I am trying to use the method shown in HSV's example file. I've updated the formulas to work on my sheet. If I just click the validation arrow, then my list drops for selection. However, I'm having difficulty when I actually go to type a letter & click the validation arrow. Regardless of what letter I type, I get a dialog stating:

"The value you entered is not valid.

A user has restricted values that can be entered into this cell."