Autocomplete as you type drop-down list. Possible? (Excel Mac)

[SHEETMAGIC]

Hi folks,

I've looked high and low for a solution to this. I found nothing, so I am thinking it's not possible. And yet I find that hard to believe... so I am hoping an Excel pro can prove otherwise.

I have lists with in excess of 10,000 entries. Some have a few thousand, some over 10K.

What I was expecting is that when I set up those lists as validation lists for cell input, I would be able to just start typing and the list would move to any valid entries.

For instance...
Let's say we the following have:
AB1 - The Place
AC2 - Something else
AVL - Another one

In a cell I have that list set as a Validation list.
What I was mistakenly anticipating is that as I start typing "av" (for example) the list will drill down to the "AVL - Another One" entry. But nothing happens at all.
With many thousands of entries, it is very tedious to search through the list for each entry.

What I'd like to know is if there is any way to convert the validation list into some kind of complete-as-you-type drop-down list?

I am using Excel 2011 (v14.3.4) on Mac OS X 10.8.5

I did find many discussions about this functionality, but as far as I can tell the answers all related to using VBA on Excel for Windows. From past experience, I recall VBA solutions will not work on Mac. I might be wrong, and I am about to test some of the solutions provided. The search I did was: http://www.mrexcel.com/forum/search.php?searchid=462946

With thanks,


Jonathan

 

[SHEETMAGIC]

Some of the solutions I found, which I had hoped would work, are as follows:
http://www.mrexcel.com/forum/excel-questions/687932-smart-search-excel.html#post3404013

Was not able to validate the VBA code. The following lines were not allowed:

Public Const gsWorksheetName As String = "COSTS"
Public Const gsDropDownDisplayRange As String = "B3:B10"
Public Const gsTemporaryListFirstCell As String = "E3"
Public Const gsNamedRange As String = "Countries"
Public Const gsNamedRangeReduced As String = "ReducedCountries"

They would generate the error: "Compile Error: Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules"

I must say, I had no idea what "Step 3 - Standard Code Module" was referring to in the instructions provided with that solution. So there's a high probability I didn't put the "Step 3" code in the right location.

http://www.mrexcel.com/forum/excel-questions/687932-smart-search-excel.html#post3403993
This one I downloaded the provided example workbook. Nothing worked at all. Although I gather it was design to do exactly what I wanted (just not on Mac).

ALSO... FYI... The list I am pulling from is alphabetically sorted.

 

[SHEETMAGIC]

I did find a solution that functions, although it is rather inconvenient to implement. I found it here:
Auto-complete Feature Cell Drop Down

The issue is that when the list is 5000+ entries, it's somewhat of a mission to insert this into the sheet I am working on. I have to insert 5000+ rows into the sheet above the rows where I am entering my data. And I have multiple columns that call on a couple of different lists.

So a macro or Mac compatible VBA solution still seems more suitable.

 

[Jim Gordon]

First, VBA is supported in Excel 2011. Solutions you find should work as is or with minor tweaking provided the solutions do not rely on Active-X controls, which are not supported on the Mac and won't be until someone ports them (they're open source).


There are various form controls available. I think if you hunt around you can find examples of how to make a combo-list edit control work using autocomplete. There are two catches. First, the combo-list edit control is available only on dialog sheets (Insert > Sheet > Dialog Sheet). You would need to trap an event (double-click a cell, for example) to trigger the dialog. If you prefer, you could use a UserForm control instead of the dialog, but there's no combo list edit control in Userforms, so you would need to do a workaround. However, an overriding concern is the maximum number of rows that are suppored by the combo-list edit control. My memory says that it's limited to 5,000 rows, so your data may not be able to be displayed by this control. I did a quick search but could not verify my suspicion.


If you are willing to have the data in one workbook, and put the the worksheet on which data validation is needed into its own workbook, you could use ODBC via a parameter query to validate the input. There are many approaches you could take to building an autocomplete routine using data retreived via ODBC from the data source.

 

[leahf]

Hi, I'm Leah. I have the same type of problem, except I would like to be able to create drop-down lists with auto-complete functionality for a list of no more than 500 items.

The biggest problem seems to be, I have Excel 2008 for Mac.

I have neither the option to create a combo box from the Developer button, nor do I see the Tools>Options functionality, in this version of Excel.

Do I have any other choice but to purchase Excel 2011 in order to enable the AutoComplete feature?

I'm not a programmer, so beyond basic code, I am out of my league.

Thank you,

L

 

[Jim Gordon]

Excel 2008 does not support Macros. Period. I can't think of a way to implement AutoComplete in Excel 2008.

Excel 2011 is nearing the end of its life. Yesterday Microsoft announced the free preview of Office 2016. If you have a Mac running Yosemite, then you might want to try that version. If your Mac is older and doesn't have Yosemite, then get 2011 and you'll be able to use it until you upgrade your Mac. Or get an Office 365 subscript, which includes Office 2011 and the ability to upgrade to the finished version of Office 2016 once it comes it.

 

[Jim Gordon]

Support for Office 2011 for Mac ends October 2017. The VB Editor in Office 2016 is a disaster - utterly useless. Microsoft has promised that a replacement VB Editor is being made but has provided no information about when the editor will be released. I imagine that only Office 365 subscribers will get the editor when it becomes available. The editor was described as being the 2011 editor ported to 2016, which suggests Microsoft is not porting the Windows VB Editor to the Mac. So much for Satya Nadella's single code base concept where Office is platform agnostic. So for now, if you want to do anything with VBA, get 2011. My guess is that pretty soon the new VB Editor will be available. I'm hoping for a big announcement in November at the MVP Summit, but that's only a hope.