Source
r/excel•
binaryvisions

Data validation list is not auto-completing correctly

unsolved

I have an Excel workbook for tracking beers.

Sheet 1 ("Beers") is a list of the beers along with the breweries. Sheet 2 ("Breweries") is a 2-column sheet with a list of breweries and their location. All sheets have headers.

I have a data validation formula on the Brewery column on the Beer sheet so that it becomes a pick list from the Breweries sheet:

=Breweries!$A:$A

Unfortunately, the auto-complete seems to be very unpredictable. When I start typing, the resulting autofill options seems random and incomplete - for example, I just tried typing St and the resulting list of options were just two, even though I have many more than that which start with St. If I type Sto it autocompletes to Stoneface Brewing with no list, despite there being multiple options that start with Sto. If I keep typing, with, say, Stou the autocomplete just drops off entirely despite there being a matching entry for Stoudts Brewing Company.

The data validation works (if I type Stoudts Brewing Company it gets accepted, and if I type something not on the list, it is rejected). It's just the autocomplete that's not functioning correctly.

How can I get the autocomplete to work reliably and select from my entire list?

Posted May 28, 2021, 5:43 PM UTC

111
AutoModeratorMOD•

u/binaryvisions - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1
CFAman2906•

That's not how data validation works. The autocomplete is from XL seeing what other options are in the same column already (e.g., above the current cell) and can suggest ideas based on that.

Your DV list is simply a dropdown of all the choices. Note that by picking all of col A, you told your list you had over a million choices, so the scroll bar on that will be quite intense.

If you really need an autocomplete form, you'll need to use the Developer - Insert - ActiveX - ComboBox. This free floating form works similar to a DV dropdown, but you can enable auto fill (as well as many other options like match first letter, must match, etc.).

1
binaryvisionsOP•

Okay, that's helpful, thanks.

The ComboBox seems to provide the functionality I'm looking for, but it's very poorly integrated as a free-floating entity. Basically not useful for a sheet with thousands of lines in it. There's no way to integrate this into the cells?

The autofill based on cells above should actually work just fine, since all of the Breweries exist as lines above where I'm typing, but I get the noted behavior - perhaps the AutoFill doesn't like the fact that the sheet is a few thousand lines long?

1
CFAman2906•

perhaps the AutoFill doesn't like the fact that the sheet is a few thousand lines long?

That could be it. It also doesn't like blanks/gaps between items. So, if A2:A10 are filled, A11 is blank, and you start to type into A12, it might not know that A2:A10 are part of your list.

1
binaryvisionsOP•

There are definitely no gaps on the list.

There are 2400+ rows, like this:

DateBreweryLocationBeerGrade
2018-12-22Captain Lawrence Brewing CompanyElmsford, NYPowder Dreams IPAA-

No blanks. In the example I used in the OP, all of the breweries (that I thought I was referencing out of my dedicated sheet) existed above the row I was typing in. In fact, if I go to the last row right now and type "Captain" into the "Brewery" column, I get zero auto-completes.

1
CFAman2906•

Are there multiple different items that start with "Captain?

If you go to a blank cell and press Alt+DownArrow do you get a small list?

1
OldElvis16•

Have you looked to make sure that there are no leading spaces in your brewery list?

1
OldElvis16•

Not sure if it is related, but is your list of Breweries Alphabetized? This might be your issue

1
binaryvisionsOP•

In my separate sheet they're alphabetized. But as noted in the other comment chain, it sounds like that's not where the autocomplete is sourced from. The Beers sheet is sorted by date, so the breweries are not alphabetized.

1