New function FILTER

[ClaudioR]

hi, I welcomed the FILTER function on LO, however I would like a clarification please because it seems to me to present an anomaly.

I start from a matrix where in the first column there are alphanumeric acronyms and in the two subsequent columns of numbers; I then execute the formula filtering on the first column and the result is this:

if there is more than one alphanumeric acronym, then it works correctly and reports the correct rows and in the remaining part of the matrix #N/A
if there is only one alphanumeric string, then it repeats the corresponding row for the entire filtered matrix
the behavior does not seem correct to me, am I perhaps doing something wrong? Thank you for your observations
In the attached file for tests in cell I2 there is a drop-down menu where you can choose the value of the string.

FILTER_prova.ods (17.1 KB)

[Lupp]

Not specific for any new function!

This is standard behavior of array functions locking an output range if the actually needed range shrinks to a single cell. I also get it with user defined functions.
I don’t know if Excel does it the same way. Do you?
See attached example:
disask109826_SingleValueToLockedRange.ods (11.3 KB)

[Regina]

I have discussed the problem in 162581 – New Filter Function is bugged and cannot detect amount of outputs correctly based off inputs from a table. The current behavior conforms to section “Non-Scalar Evaluation” (section 3.3, part 4, ODF standard).

[Lupp]

If you are interested in my twisted ideas you can check the attached example.
It contains an (already extended) set of functions that allow to use expressions with array-output without any locked ranges, and without preset limits to the size of the returned array.
nonSpillingUsageOf_SORTBYandFILTER.ods (35.0 KB)
The first working implementation was less than 60 lines of Basic code.
=== Edit ===
Forgot to mention hat the example needs V 24.8.0 or higher.
=== /Edit ===

[Villeroy]

Pass Entire sheet to a formula, is this possible?

And while we are in it, Base can represent Calc “tables”, so they can be queried by plain, simple SQL. A rectangle of Calc cells qualifies as a pseudo-table if the table is a normalized one that qualifies for a pivot table according to Database normalization description - Microsoft 365 Apps | Microsoft Learn the table represents some sheet’s one and only content if there is other content on the same sheet, you can isolate the table as a database range (menu:Data>Define…) After connecting a B…