Calc - Searchable Drop-down list without using mouse

[daveb3030]

I am going for a way to make a drop-down list display only the matches that correspond to the text that I enter into a cell, and for it to update the more characters that I type. I have a named range that I want to search through. I have tried both of the following but neither is want I am looking for. In my target cells I have selected
1.) Data - Validity - Allow List - pasted the list into the Entries Box
2.) Data - Cell Range - Source - type in the Managed Name

Both of these approaches require me to click on the down arrow to select the appropriate data.
I do not want to have to use the mouse to select the correct data. Ideally I would like to type a few characters and either hit enter if it matches, or use the down arrow to select the correct data and then hit enter.

Any help would be greatly appreciated.
Dave

[Wanderer]

@daveb3030

1.) Data - Validity - Allow List -

Try Alt-Down key to open the List via keyboard.

[daveb3030]

Thanks Wanderer, I was not aware of that keyboard shortcut. This is certainly far better than using the mouse to select the data. I would still like to be able to just typing in the cell and see the list dynamically narrow to fit the typed data. But if that is not possible I will use this as an alternate solution.
Thanks

[lodf2023]

The list won’t narrow, but it will position the cursor.

In addition…

More info can be found in the Calc Guide - Validating Cell Content - Show selection list.

The Calc Guide has a section for Keyboard Shortcuts.

Shortcuts can be viewed or customized using Tools > Customize > Keyboard.

[Lupp]

A detailed example with randomly generated "Words".
The validation is automatically updated and filtered regarding a partial content.
disask_131314_UpdatingAndFilteringValidationDropdown.ods (51.1 KB)

[lodf2023]

Cool.

So the key is the Data Validity Source formula:
FILTER(INDIRECT($A$2), ISNUMBER(FIND(UPPER($C4), UPPER(INDIRECT($A$2)))), "")

[daveb3030]

Thanks Lupp & lodf2023, I’l try to implement this if I can figure it out.

[schiavinatto]

Taking advantage of @Lupp’s suggestion,

When filling in the FILTER
If there are available choices
a list will be opened for selection.


If you want selection by initial
Change the validation from >=1 to =1.

disask_131314_UpdatingAndFilteringValidationDropdown_GS.ods (21,1,KB)

[Lupp]

@schiavinatto

If there are available choices``...

I would assume that the entries in Column A are supposed to act as primary keys to datasets. This requires that all the keys are distinct. If there are repetitions the PK functionality can’t work.

@daveb3030

...and for it to update the more characters that I type.

Fields where you can enter starting portions of keys and get shown a list of still possible choices are used for databases. To make Calc simulate such a behaviour you would need some rather complicated user code. Somebody needing this functionality should better switch to a database.

My suggestions are not really made for actual use, but “for fun”. I tried to do it

• without a need to use the mouse. (Requires a shortcut for dropdown validity list.)
• without user code.

There is a new example which also allows the selection by regular expressions:
disask_131314_UpdatingAndFilteringValidationDropdown_Regex.ods (19.8 KB)

The updating of the validation lists still requires that you quit editing of the respective filter string.