Searchable drop down list calc

Is there any way to make a searchable drop down list in calc without using a macro?

and Using macro? I accept macro suggestions in basic or python

Could someone provide me with a tutorial on this?
Thanks

like this:


Complement:

Here’s an example of how I’m using it, following the tips below

I added an auxiliary column to remove the empty cells. with this formula:

=IFERROR(INDEX($B$2:$B$5;AGGREGATE(15;6;(ROW($B$2:$B$5)-ROW($B$1))/($B$2:$B$5<>"");ROWS(F$2:$F2)));"")

And in the dropbox data validation this formula:

OFFSET($C$1;1;;COUNTIF($C$2:$C$20;"?*"))

Is there a better method?

test dropbox.ods

How would I go about inserting searchable dropbox into all rows of column E?

a searchable drop down list

It may be possible to do both with and without a macro. Just tell us in more detail what exactly “searchable list” means.

Something like this video to excel. However, in calc we do not have the FILTER and SORT function

FILTER? SORT? Is it a formula like

IF(<cell with dropbox>="";<full list>;IF(ISNUMBER(SEARCH(<cell with dropbox>;<full list>));<full list>;""))

and checkbox Sort entries ascending won’t give you similar results?

Very good, helped a lot.

In data validation, when I indicate the range eg: $D$2:$D$100 with the formulas to do the validation. In the cell with the dropbox an empty field will still be displayed for (which are the non-coincident cases), would it be possible not to show this empty option in the list?

Another curiosity, in the excel video, the character # was used in the data validation formula =MasterData!$D$2# to automatically get the last item in the column. Is there anything similar in Calc?

I added an complement with an example

Re #: No. And it’s not the last item in a column but the last item of a dynamic array spill range, which are new features since Excel 2019.

The best (and overtaking Excel) solution would be to add the “Search items …” field to the selection dialog of the drop-down list box (as it is done in AutoFilter). : )

Hello John,

All my wishes for this new year.

Hopefully you could be able to help me.

I think I am looking at what you have been explaining, unfortunately for me my scripting knowledge is near below ground.
I implemented your “sortwithfilter” script to my file but when I “activate” it on a cell, and then try it, nothing expected happens and if I click on the dropdown arrow then I have the following error: “Basic runtime error, arguments is not optional” and it opens the script at “If InStr(aData(i,j), sFilter) > 0”

What I would like to ease my job is be able to type in the dropdown list, in column D, the value 80 for example and then it would show me all the “lines” containing 80 in that dropdown list.

I join the file I have constructed and I have implemented your function in cell D106 of sheet “Add FicheTechnique”.

You can find the file at main.ods - Google Drive

Regards,
James

Happy New Year James! Best wishes (especially - health, today it is relevant)!

Try to write the formula
SORTWITHFILTER($'data for 12 - Add Fiche Technique'.$A$4:$A$304;D106)

Now type 80 and press Alt+Down Arrow.
Filter80

Please do not limit the range of values to $A$191, give much more. The macro expected to encounter at least one blank cell in the specified range.

thank you for your prompt answer but there must be something I miss somewhere.

Doing the following
allow empty cells
show selection list
sort entries ascending (same if unchecked)

SORTWITHFILTER($‘data for 12 - Add Fiche Technique’.$A$4:$A$500,D160) (;D160 reverts to ,D160 due to my english language pref I guess)

then if I enter 80 and hit left or right ALT (on my Macos Monterey keyboard) and arrow down

image

for the sake of it I just tried in a vmware fusion pro 12 windows 10 VM and I have the same problem.

driving me nuts :grinning:

Well, please try cells D103:D110 in this spreadsheet. If this works, then let’s try to figure out what you are doing wrong. (If it doesn’t work, let’s think what I did wrong)

here is what I tried, I wonder if I am hitting a bug or else.

maybe I should remove LibreOffice and all user’s information/cache related to it?
remove/change java?

https://drive.google.com/drive/folders/1rvORmfE52vDq6BSKZeoawUDzo6XnV4We?usp=sharing

Well, if you don’t like that one blank line at the beginning of the list, you can use a function like this:

Option Explicit

Function SortWithFilter(aData As Variant, sFilter As String) As Variant
Dim i As Long, j As Long 
Dim aRes As Variant 
	aRes = Array()
	For i = LBound(aData) To UBound(aData)
		For j = LBound(aData,2) To UBound(aData,2)
			If InStr(aData(i,j), sFilter) > 0 Then AddOrInsert(aRes, aData(i,j))
		Next j
	Next i
	If UBound(aRes) <= LBound(aRes) Then
		SortWithFilter = aData
	Else
		SortWithFilter = aRes
	EndIf
End Function

Sub AddOrInsert(aData As Variant, key As Variant)
Dim l&, r&, m&, N&, i&
    l = LBound(aData)
    r = UBound(aData) + 1
    N = r
    While (l < r)
        m = l + Int((r - l) / 2)
        If aData(m) < key Then l = m + 1 Else r = m
    Wend
    If r = N Then
        ReDim Preserve aData(0 To N)
        aData(N) = key
    ElseIf aData(r) = key Then
'        Already in this array - nothing to do '
    Else
        ReDim Preserve aData(0 To N)
        For i = N - 1 To r Step -1
            aData(i + 1) = aData(i)
        Next i
        aData(r) = key
    End If
End Sub

By the way, you do not need auxiliary ranges and intermediate calculations - insert the formula directly into Validity - Criteria - Source

Validity-Source.png

Update You can use this version of the main function

Function SortWithFilter(aData As Variant, sFilter As String) As Variant
Dim i As Long, j As Long 
Dim aRes As Variant, aFullData As Variant
	aRes = Array()
	aFullData = Array()
	For i = LBound(aData) To UBound(aData)
		For j = LBound(aData,2) To UBound(aData,2)
			If IsEmpty(aData(i,j)) Then
				SortWithFilter = aRes
				If (UBound(aRes) < LBound(aRes)) Then 
					SortWithFilter = aFullData
				ElseIf ((UBound(aRes) = 0) And (aRes(0)=sFilter)) Then
					SortWithFilter = aFullData
				EndIf 	
				Exit Function
			EndIf 
			If Trim(aData(i,j))<>"" Then
				AddOrInsert(aFullData, aData(i,j))
				If InStr(aData(i,j), sFilter) > 0 Then AddOrInsert(aRes, aData(i,j))
			EndIf 
		Next j
	Next i
End Function

In this case, you can set once and for all the initial range of any size - the selection list will be formed only for the existing values - SORTWITHFILTER($A$2:$A$1048576;E2)

It didn’t work as expected, when I type ‘ri’ it displays all options that have the characters ‘ri’ as expected, in the case of the test file → 4 names.

However, when I type ‘bri’ it displays the list with all the names, instead of showing the list only ‘Brian’.

That is, when there is only one match, it is not working

test dropbox_new.ods

Would you know how to fix this?

Yes, it was designed that way. If you already have Brian selected, you will not see a list for choosing another option - that is why the condition If UBound(aRes) <= LBound(aRes) Then was introduced. If, for example, Alex is selected and both Alex and Alexander are present in the list, then you will see both names (and only this items). You can change <= to strict <. Then if Brian is already selected you will see a list of only Brian. If you think this behavior is preferable, then feel free to change the code as you see fit.

You can change the final check as follows:

SortWithFilter = aRes
If (UBound(aRes) < LBound(aRes)) Then 
	SortWithFilter = aData
ElseIf ((UBound(aRes) = 0) And (aRes(0)=sFilter)) Then
	SortWithFilter = aData
EndIf

Excellent, thanks

One last doubt, if I want to define the cell range “aData” via basic programming, how would I do it?

I’m doing it like this but I’m learning an error:

Function SortWithFilter(sFilter As String) As Variant
	Dim i As Long, j As Long
	Dim aRes As Variant

	Dim aData as Variant

	aData = Array(51)
	oSheet = ThisComponent.Sheets.getByName("myBase")
	aData = oSheet.getCellRangebyName("A2:A53")
	...

Oh, aData will be oSheet.getCellRangebyName("A2:A53").getDataArray() and

For j = LBound(aData(i)) To UBound(aData(i))
        If InStr(aData(i)(j), sFilter) > 0 Then AddOrInsert(aRes, aData(i)(j))
    Next j

Why you don’t want send values of range as param?