Created attachment 194808 [details] SORT function on database range If the SORT function is based on a database range and the content of the database range becomes larger, then the output range of the SORT function does not expand to the new size of the source range. Recalculate Hard does not expands the output range of the SORT function. There is even no adaption, when the file is saved and reloaded. How to reproduce: Open attached file. It has formula SORT(Members_data;2) and Members_data=$Sheet1.$A$2:$B$10. Insert an entry in the green area between row 5 and 6 e.g. "Tim Hof". Verify in Data > Define Range, that it is now Members_data=$Sheet1.$A$2:$B$11. The new entry is visible in the output of the SORT function, but the output range does not expand, so the entry "Eva Waldeck" is no longer visible. Save and reload the file. The output range is still wrong although the formula is {=SORT(Members_data;2)} and Members_data=$Sheet1.$A$2:$B$11
I think the problem is the formula converted to an array {} formula. If I remember correctly, a similar situation happened with some recent new functions. BTW the file does not work in Excel, only for the formula as array {}. It seems that the implementation of dynamic arrays, is pending.
(In reply to m_a_riosv from comment #1) > It seems that the implementation of dynamic arrays, is pending. Of course dynamic arrays has not been implemented in Calc yet. New Calc functions for compatibility with Excel 2021 were recently implemented (and possibly still some issues may arise). But the whole set of "Dynamic arrays" features is much bigger than adding a couple of functions. There are syntax changes (or additions), new error codes/messages, automatic resize of arrays (both for input data and results)...
The SORT function calculates the whole array, but uses the existing range. This can indeed only be solved, when the "dynamic array" feature is implemented. I therefore add a dependency to the corresponding feature request in bug 127808.