Make drop down list bigger? AND Auto-predict in drop down list?

I have a drop down list that I'd like to increase the vertical height of when I click on the cell, as it is a long list and it'd be easier to see more entries at a time.

Also, I'd like to know if it's possible to write in the cell and have it auto-predict based on the drop down list it is pointing to, which sometimes would be easier than scrolling through the long list for the correct entry.

Are these things possible?

See attached as example of "predictive" list

It works fine for me: This is a "standard" file I have so used "as-is"

It may (does!) not work with your 2003 version as I received "warning" messages about compatibility when saving as Excel 97-2003 format.

Originally Posted by JohnTopley

It works fine for me: This is a "standard" file I have so used "as-is"

It may (does!) not work with your 2003 version as I received "warning" messages about compatibility when saving as Excel 97-2003 format.

Ah, then that's the problem. :-/ Is it difficult to set up the auto-predict from scratch?

Try this in column G (replacing IFERROR)



=IF(ISERROR(VLOOKUP(ROWS($G$2:G2),$E$2:$F$100,2,0)),"",VLOOKUP(ROWS($G$2:G2),$E$2:$F$100,2,0))

copy down

Originally Posted by JohnTopley

Try this in column G (replacing IFERROR)



=IF(ISERROR(VLOOKUP(ROWS($G$2:G2),$E$2:$F$100,2,0)),"",VLOOKUP(ROWS($G$2:G2),$E$2:$F$100,2,0))

copy down

Okay, I can see it now. But I'm not sure if this solves my problem. I'd like the cell, say cell A1, to either be able to pull up a drop down that I can click the correct value for (it is set up this way now), OR I can type in A1 and it will predict the rest, like Google does for instance.

In the file: if you start typing in C2 and click drop down you will get list starting with as many characters as you have typed;

Type in "p" to get a list: type in "pe" to get a shorter list

Originally Posted by JohnTopley

In the file: if you start typing in C2 and click drop down you will get list starting with as many characters as you have typed;

Type in "p" to get a list: type in "pe" to get a shorter list

I see. That is really cool but what I am hoping for is that C2 would "suggest" the next alphabetically possible finish to what I type. Like Google does.

I think comparing this "look ahead" with the sophistication of "Google" is a step to far! Google has complex algorithms which it uses, far beyond the capabilities of Excel.

Even excel 2003 can be more sophisticated than you expect John.
_____
maybe not perfect but it is close to what was asked

This is one of the workbooks I have "on the schelf"

The combobox will pop up when you click a cell in the target column.
The combobox will scroll to available matches and autocomplete in the inputfield

thru properties > listrow setting you can set how many options will be shown

selected item in combobox will be writen to underlying cell

@Roel
Not very different to the one I posted. Less sophisticated (!) than the one I posted, which limits the list to those "words" starting with the typed text.

From the OP ...

C2 would "suggest" the next alphabetically possible finish

I suppose it depend how one interprets the "suggest" but Google appears to predict on your last few entries and/or frequency of use of a particular search string.

Originally Posted by Roel Jongman

Even excel 2003 can be more sophisticated than you expect John.
_____
maybe not perfect but it is close to what was asked

This is one of the workbooks I have "on the schelf"

The combobox will pop up when you click a cell in the target column.
The combobox will scroll to available matches and autocomplete in the inputfield

thru properties > listrow setting you can set how many options will be shown

selected item in combobox will be writen to underlying cell

This is exactly what I'm looking for! Two questions, how do I make my drop down lists do this? Also, Where is "properties listrow"? Thanks.

You will need to make a combobox in your sheet and make sure the name is Combobox1 or rename all Combobox1 references to the actual name of the box in the VBA code.

All the VBA code you need is in CodeSheet of the worksheet (access thru ALT-F11 and double click "Blad1(Invoer)" to see the code
The properties you set while the combobox1 is selected (right click for menu to select properties then find the option "listRows in the alfabetical properties list
this the Microsoft support page to insert a combobox, make sure you insert the ActiveX version .
if needed google for other helppages how to set the properties if above comments do not work for you

It may take you a little trail and error to get it working but the code is not that diffucult.

do you mean smth like that

Originally Posted by Roel Jongman

You will need to make a combobox in your sheet and make sure the name is Combobox1 or rename all Combobox1 references to the actual name of the box in the VBA code.

All the VBA code you need is in CodeSheet of the worksheet (access thru ALT-F11 and double click "Blad1(Invoer)" to see the code
The properties you set while the combobox1 is selected (right click for menu to select properties then find the option "listRows in the alfabetical properties list
this the Microsoft support page to insert a combobox, make sure you insert the ActiveX version .
if needed google for other helppages how to set the properties if above comments do not work for you

It may take you a little trail and error to get it working but the code is not that diffucult.

Hmm. I'm not seeing the Blad1(Invoer). I just see Sheet1 and ThisWorkbook. Both are empty. :-/