https://bugs.documentfoundation.org/show_bug.cgi?id=170784
--- Comment #7 from ady <[email protected]> --- (In reply to Andreas Säger from comment #6) > @ady > > Have a close look at the formula Sheet5.A2: > =SUBTOTAL(9;$Sheet1.$A$1:$Sheet4.$A$19) > Clear Sheet1.A17:A19 and re-enter the formula on Sheet5.A2. It changes to > =SUBTOTAL(9;$Sheet1.$A$1:$Sheet4.$A$16) I have had to re-read those instructions several times and "play" with Calc, in order to be able to modify them and to be precise with the exact steps, so to be able to replicate the behavior. I am sure that a simpler case file can be generated. 1. Open attachment 205505. 2. Take note of the current formula in $Sheet5.A2: =SUBTOTAL(9;$Sheet1.$A$1:$Sheet4.$A$16) (the result is 55, which is the correct result for such formula on that precise 3D range, ending on row 16). 2.1. Edit $Sheet5.A2 from: =SUBTOTAL(9;$Sheet1.$A$1:$Sheet4.$A$16) to: =SUBTOTAL(9;$Sheet1.$A$1:$Sheet4.$A$100) (i.e. change the last row address from 16 to 100) 2.2. After editing the formula, press [ENTER]. Then go back to cell A2. The formula is now: =SUBTOTAL(9;$Sheet1.$A$1:$Sheet4.$A$19) Note the last row address of the formula (19, not 100), and the new result (60). 3. Go to $Sheet1; select the area $Sheet1.$A$17:$Sheet1.$B$19 and delete the _contents_ of that range. 4.1 Go to $Sheet5.A2 and edit the formula, from: =SUBTOTAL(9;$Sheet1.$A$1:$Sheet4.$A$19) to: =SUBTOTAL(9;$Sheet1.$A$1:$Sheet4.$A$100) (i.e. change the last row address from 19 to 100) 4.2. After editing the formula, press [ENTER]. Then go back to cell A2. The formula is now: =SUBTOTAL(9;$Sheet1.$A$1:$Sheet4.$A$16) Note the last row address of the formula (16, not 100), and the new result (55). Conclusion: SUBTOTAL() automatically shrinks the range that was initially typed-in according to the last-non-blank (and non-hidden) cell address of the first range of the 3D range, so it misses part of the ranges in the second-to-last ranges of the 3D range. Whether this is either intentional, somehow unsupported, or it is a bug, IDK. -- You are receiving this mail because: You are the assignee for the bug.
