https://bugs.documentfoundation.org/show_bug.cgi?id=145640
--- Comment #5 from Colin <[email protected]> --- (In reply to Eike Rathke from comment #4) > Note that the expression =SUM(F10:G10:H10:I10:J10:K10) uses the ':' range > operator repeatedly to extend the range, effectively resulting in the range > F10:K10. Apparently that somehow makes a difference when the references are > updated during sort that then forms for example > =SUM(F21:G21:H21:I21:J21:K21) but the internal reference does not result in > the effective range F21:K21. Re-entering the formula, by Function Wizard or > editing the formula, of course produces the expected result again. I explained why the syntax might appear odd. I never rationalised it. The major problem I have encountered is that if a formula in a range to be sorted contains a "normally" defined range viz if(L10>5;SUM(G10:G40);"") then G10:G40 can end up anywhere in the column and the formula doesn't follow the new locations. In my use, they don't randomise but normally it's a Z-A sort so the cells end up in reverse order. If the formula is changed to read ;G10+G11+G12+G13................then it keeps perfect track of the new locations. However, if one of those accumulating cells contains null "" then the summation fails with the wrong data type. At least the array function SUM() seems to correctly filter the alpha characters to return a numerical result. I was trying to "engineer" a syntax that would correctly track the cells when the array is nested into a function. -- You are receiving this mail because: You are the assignee for the bug.
