[Libreoffice-bugs] [Bug 138896] AutoFilter sort reference not updated with nested IF statement

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=138896

--- Comment #11 from Colin <that.man.colin at gmail.com> ---
(In reply to Mike Kaganski from comment #10)
> I think steps in comment 0 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 :).

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20201219/92e66524/attachment.htm>