Auto Complete in a Data Validation cell technique (feedback wanted)

Here is my idea presented for Auto Complete Assist in a Data Validation Drop Down Cell.

Auto Complete Assist

It's not a true autocomplete, but it was a fun technique to layout and I'd like some feedback to iron out the wrinkles you may find.

Thanks in advance for the feedback.


(also called for feedback from here: http://www.mrexcel.com/forum/showthr...23#post2247523)

Edit: Deleted. I'm a moron.

That works for one cell, but for a form where I'm going to have a column of cells using this same drop down, that becomes impractical I think. Your method would require two helper columns that stretched the entire data set PER CELL that was going to use the lists. I would only need to go down 130 rows and it can no longer be done in Excel 2003...you run out of columns...and your sheet would be huge by then!

Better to have the lists laid out one time in multiple columns/ranges to start with and then an infinite number of cells can use them.

Keep it coming...!

JB,

I think this is a great technique and, frankly, don't see how the lists can be further refined given the wide disparity of possible names.

It isn't intuitive to select from twice from separate lists the same drop down, so possibly the first selection in each name list might be "choose a name". You included notes to tell us to do this but, obviously, such a note as presented would not be shown in the finished product.

I like this and, shamelessly, will "borrow" it. Thanks for sharing.

Palmetto

Originally Posted by Palmetto

It isn't intuitive to select from twice from separate lists the same drop down

Good point, in application, yes I would have to include some training to aid them in getting familiar with the technique.

I like this and, shamelessly, will "borrow" it. Thanks for sharing.

That's the goal! Go for it. When you do, please come back and feedback how it actually pans out in your real application. I can fathom how only two cells could be used to choose from a wide variety of "options" for customizing a product, like a T-Shirt, you know? Would like to see this actually work out for use.

Jerry

Haven't had time to think about this in a while, but I was determined to find a way to do it. I found a formula that can work with data validation, so no other cells are needed, yet still matches based on multiple first letters, making it useful if you have an exceedingly long list and eliminating the need to break up the original list. It can be copied over, down, etc. I don't know whether it is more or less efficient than yours from a calculation speed standpoint, but it doesn't need Indirect to work. If you type in a letter beyond the list, it simply returns the last available entry as the only one in the dropdown.

You may have to test it, but in my limited testing, it seems to work fine, assuming your list is in alphabetical order.

*Edit: simplified formula slightly.

That's pretty slick. It's an interesting side-effect that selecting any name gives you a drop down with that name and the next one. I can think of a couple of uses for that!

Want to play with it to see if you can get it to maintain my original design that after any name is chosen, the drop down in that cell displays all the names starting with same first letter? Choosing Cherise would still show all "C" names...?

Hmm, still not getting the intended results. When I use the second code, selecting the same cell again simply presents the entire list again.

The goal is to maintain the 2nd use feature of my original column B on Sheet2...second use shows all the names of that letter group.

The lists being hidden on a second sheet I thought was a clear prerequisite, too. Sorry if it wasn't. The resulting formulas used would need to be on Sheet2. That's why I used named ranges in my setup, to make it simple to maintain.

Looking at your new sheet, it still behaves a little oddly.

1) Choosing Cherise does present the "C" names when you look again, but typing in Andy doesn't. There seems to be no "A" list of names.

2) There is no default list anymore, perhaps not much of an issue.

My original technique at least "moved you through" the process of presenting subset choices. Also it did that, too, even if the cell was blank or had a space in it...

========
As for helper cells, I wouldn't want that, but for your own development, that might be a possibility. I'm still looking to maintain the single-cell technique with all the features of the original, for now.

1) Empty or spaces in a cell presents a sub-list of name "groups"
2) After making a choice, the list shows all names from that group
3) After making a final name choice, the list keeps showing that group until cell is cleared.

Heh..."apparently"...funny how one word can color a whole phrase.

Anyway, I was able to take your formula and tweak it back to my original criteria. So this does improve the implementation for an alphabetic listing. It allows us to put it back into a single column and eliminate the need for all the other named range lists.

Now we're down to 2...the list of "alpha-categories" and the main listing of names. The final formula I used is:

=IF(TRIM(B2)="",AlphaList,INDEX(NameList,IF(ISERROR(MATCH(LEFT(B2)&"*",NameList,0)),MATCH("*",NameList),MATCH(LEFT(B2)&"*",NameList,0))):INDEX(NameList,MATCH(LEFT(B2)&"zzz",NameList)))

I was able to get the multi-category version to work even better.

Download: FILE PAGE
(the file to download is called: AutoCompleteDataValidation(SortOf)3.xls)

This works even better, built off of the work done with the single column v.2 previously, this gives us the same technique in multiple lists that do not have to be alphabetic.

Have a look.

There are now 3 separate pages showing 3 different implementations of this technique:

Same Cell Cascading Dependent Drop Down

Phew...

We're up too 4 different pages of this technique, but I've updated a couple of these to now be xl07+ compatible. xl07+ no longer allow array/union formulas in data validation, but the technique still holds good moving the formula into a Named Formula instead.

Updated Data Validation Sub List Techniques

You're talking about COMBO BOX and the autocomplete function built into it. This thread is purely for Data Validation, which does not have true autocomplete as an option. This whole thread is about a middling workaround, there's no way to turn a DV list into a true autocomplete. That's what combo boxes are for.

Another fully presented method, Dynamic Cascading Drop Downs that put all the work into the data validation formula so there is no Indirect() issue:

http://www.excelforum.com/excel-tips...t-problem.html