<html> <head> <base href="https://bugs.documentfoundation.org/"> </head> <body> <div> <a class="bz_bug_link bz_status_UNCONFIRMED " title="UNCONFIRMED - AutoFilter sort reference not updated with nested IF statement" href="https://bugs.documentfoundation.org/show_bug.cgi?id=138896#c11">Comment # 11</a> on <a class="bz_bug_link bz_status_UNCONFIRMED " title="UNCONFIRMED - AutoFilter sort reference not updated with nested IF statement" href="https://bugs.documentfoundation.org/show_bug.cgi?id=138896">bug 138896</a> from <a class="email" href="mailto:that.man.colin@gmail.com" title="Colin <that.man.colin@gmail.com>"> Colin</a> <pre>(In reply to Mike Kaganski from <a href="show_bug.cgi?id=138896#c10">comment #10</a>) > I think steps in <a href="show_bug.cgi?id=138896#c0">comment 0</a> are pretty clear. > And I repro the problem as described, using "Update references when sorting > range of cells" under Options|Calc|General. > > With the mentioned setting disabled, I get a different resulting formula in > M56: it's > > > =IF(J56="";"";IF(J57="";SUM(J53:J56);"")) > > and it is much saner. I get the same result as you but on closer examination, you will discover that the cells being SUMmed are now future events for which there can never be any valid data. It should only produce a result for the current event and any pertinent "earlier" event cells. I have already established that reversing a SUM() reference viz SUM(a1:a10) gives the same result as SUM(a10:a1) but the reverse sort doesn't simply reverse the range reference - it moves the range from say current-3 to current +3 that is to say, it is no longer (J53:J56) it should become (J56:J59) - the cells move from a lower to a higher order because the array relationship is now reversed. The cells being considered are exclusively "today's" events so their offset is coded manually into the formula for each event in the daily cluster, otherwise there would be a requirement for further "is it the first or last pertinent event of the day" verification. > > I suppose that you should check if this setting solver this for you, No such luck, it still malfunctions. The setting works as anticipated for all other scenarios and if I make the global change I fear it could easily mess up many other documents where there is no second conditional range in the formula or use > things like OFFSET in your formula. My opinion is that it works as designed, > taking into account the absolute impossibility to define "updating > references to ranges (which may change randomly) when sorting". I also wondered about OFFSET but the formula would somehow have to take the sort order into consideration. How does one detect the current sort status - Ascending or Descending - and then adjust the OFFSET appropriately? If you're happy the software performs as designed and that my attempt is beyond the capabilities of a spreadsheet then it's probably best to reject the report as NOTABUG. I was just trying to put some cream on the candy - it's proving to be more of a cyanide pill :).</pre> </div> <hr> You are receiving this mail because: <ul> <li>You are the assignee for the bug.</li> </ul> </body> </html>