[Spreadsheet] FILTER function returns #N/A instead of shrinking the dynamic array when source criteria change #3576
Description
This issue is unique.
- I have used the search tool and did not find an issue describing my bug.
Operating System of DocumentServer
Linux (DEB package)
Version information
9.2.1.43
Expected Behavior
I am reporting a bug regarding the behavior of dynamic arrays in the Spreadsheet editor. When the source data for a FILTER function is modified such that the number of matching rows decreases, the dynamic array (spill range) does not shrink correctly. Instead, the "excess" rows that were previously populated now display a #N/A error.
Steps to Reproduce:
Create a data set in the range B1:G16.
In column H, add criteria values (e.g., "invalid" and "valid").
Enter the following formula in an empty cell: =FILTER(B1:G16; H1:H16="invalid"; "").
Assume the formula initially returns 5 rows based on the "invalid" criteria.
Change one of the values in column H from "invalid" to "valid".
The expected result should now only occupy 4 rows.
Actual Behavior: The 5th row of the previous result does not disappear. It remains in the spreadsheet but its content changes to #N/A. The dynamic array fails to resize/shrink its boundaries.
Expected Behavior: The dynamic array should automatically resize. The row that no longer meets the criteria should be cleared from the sheet entirely.
Additional Notes:
Workarounds: I have tested temporary workarounds, but they do not work. Re-entering the formula (pressing Enter) or using forced recalculation (Ctrl + Alt + F9) does not remove the #N/A errors.
Frequency: This issue is consistent and reproducible.
Actual Behavior
Workarounds: I have tested temporary workarounds, but they do not work. Re-entering the formula (pressing Enter) or using forced recalculation (Ctrl + Alt + F9) does not remove the #N/A errors.
Reproduction Steps
No response
Additional information
No response