https://bugs.documentfoundation.org/show_bug.cgi?id=171017
--- Comment #13 from ady <[email protected]> --- @Colin, Sorry, but there should be one bug per report. Posting a list of procedures that have not much in common is not a way to analyze the situation. I'll try to answer to your questions, but I think most of this should go to <https://ask.libreoffice.org>. First, INDEX() in particular might not be necessarily the problem, but rather one example of a generic issue regarding UNDO and array formulas. (In reply to Colin from comment #0) > 1) If the "slicer" at the top of the array is utilised FOR SORTING there is > a warning that the activity cannot take effect on only part of an array but > selecting a target value is acceptable and will produce a filter. 1) In the range S2:Y2, you have an array formula, so the range S2:Y2 behaves (or should) as an indivisible unit (a vector, or an array of 1 row x 7 columns). You cannot (or shouldn't) "break" the vector in parts. You could apply a new sort, but that should apply to the whole vector at once. If you want to "move" (by sorting) _only_ the first component of a vector, you would not have a vector anymore. Hypothetically, you could use multiple non-array formulas in S2:Y2, one per each cell. Then you could try to sort them independently, but... That range is not pure data, but a consequence of the formula. The resulting vector (or the "equivalent" range of non-array INDEX() formulas) depends on the value on column Z ($Z2 for S2:Y2). So, if you sort Z2:Z282, then the vectors will end up according to the location of the value in column Z. Whatever else the sorting of the Autofilter would do, would "break" the vectors. (In reply to Colin from comment #0) > 2) entering an "ID" number in Z3 to drive the lookup will produce data but > if the ID is then deleted or undone, not all the recipient cells return null. 2) In my settings, I use semicolon as separator of arguments in Functions, and also as separator for Array rows, while I use the vertical bar as separator for Array column. So, the original array formula in S2:Y2 looks in my system as: {=IF($Z2=0; 0; INDEX($A$2:$I$999; $Z2; {3|4|5|6|7|8|9}))} Note that, if the condition is true(), you get one value of zero (a "scalar"), but when the condition is false(), you get a vector of 1 row x 7 columns. For S3:Y3, that is: {=IF($Z3=0; 0; INDEX($A$2:$I$999; $Z3; {3|4|5|6|7|8|9}))} So, when you insert the value of 931 in Z3, you get the vector that corresponds to the relative row 931 of the reference in the INDEX() function, which happens to be in row 932, the range C932:I932. Now you change back that value in Z3, from 931 to empty or zero. The IF() function forces the resulting scalar, on the first component of the "old" vector. Imposing zeros on the rest of the range, S4:Y4 is incorrect; as incorrect as you think it is to leave the prior values. The solution, as I suggested in comment 4, is to have the "true" result of the IF() to be also a vector of 1 row x 7 columns, instead of just one scalar. The new array formula in S2:Y2 would be: {=IF($Z2=0; {0|0|0|0|0|0|0}; INDEX($A$2:$I$999; $Z2; {3|4|5|6|7|8|9}))} Then copy+paste this array formula from S2:Y2 to S3:Y282. Do not use the fill handle; use ctrl+c and then ctrl+v on the relevant ranges. This completely solves point 2. (In reply to Colin from comment #0) > 3) if A3:Q5 are selected and removed from the data source with [Ctrl]+[-] & > shift up then the first and second rows of the return are "nulled" and > Indexing appears to re-index with "random" numbers in the formula 3) After modifying the array formulas in the prior point 2, the same "nulled" happens, and it is correct. The first relative row contains the number 932 in Z2. That is the relative row of the reference table of the INDEX() function, which is (absolute) row 933 (from the total new range of A2:I996, instead of the prior A2:999). The relative row 932 (range C933:I933) has no values, thus the new result in S2:Y2. In Z3 you do not have a (relative row) number, so the result of the IF() function gives you the 7 zeroes. The new index is not random. For instance, cell Z5 contains the number 909, so the INDEX() function retrieves the range C910:I910 (the 909th vector of the reference table). (In reply to Colin from comment #0) > 4) Selecting and removing Analysis rows S4:AE4 with [Ctrl]+[-] & shift up > has a combination effect. The rows appear to remove correctly but when the > action is undone, ALL the data rows below the reinstatement adopt the refill > characteristics at 3) above. And here we have the real issue. The UNDO affects array functions, sometimes in unpredictable ways. You will find posts at <https://ask.libreoffice.org> and elsewhere. This is a similar UNDO problem as in your other items. After the undo, review the formulas in T5:Y282; they are no longer the array formulas that they used to be. The good aspect of it: it can be replicated. I would suggest taking your original file and making a new one based on it, containing only a few rows (10 should be enough), with references also limited to a few rows and a few columns (you don't need A2:I999), and the vector does not need to be 1x7 (1x3 should be enough). With such file, you could replicate the above 4th item (with the added modification of the formula as I suggested). That is a bug report that you could file, with clear steps, and pointing to the UNDO action in relation to array formulas. Here you are using INDEX(), and it is repeatable. Whether developers would consider to put the blame on INDEX() in particular, or rather on the UNDO, IDK. -- You are receiving this mail because: You are the assignee for the bug.
