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?
1 Like
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.
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
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 
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
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
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
EndIfExcellent, 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?
If I change the formula like this:
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")
...
is showing this error:
Array must be dimensionded.
Here’s an example:
Why you don’t want send values of range as param?
This is a test, but I intend to set this range as a parameter or function, At the moment if I increase the number of items on the list I would need to manually change all dropboxes. And by setting this value via basic, I can calculate the amount of total items in the list automatically.