I didn’t yet study your sugessted solutions posted in and attached to some of your posts to the mentioned thread [Solved] Index/match/max (View topic) • Apache OpenOffice Community Forum.
Before I start to use time for an investigation, I would like to know if the attachment to you question here is supposed to be a “final-so-far” version or if you are still working on the task in steps.
No. I ended my research at a dead end.
Ctrl+Shift+F9 updates all formulas on all sheets quickly enough and without errors. But editing player points does work from time to time, but more often it doesn’t.
UPDATED:
Maybe @erAck or @mikekaganski or other specialists can help the author here (I’m not the problem author)?
Function GetNextWinner(Players, Points, Winners)
On Error GoTo Failed
<...>
Exit Function
Failed:
GetNextWinner = "Error": End
End Function
And I can’t understand why the error handler doen’t work. Calc crashes somewhere before… Internal error.
I have been working with LO Calc for about 2 years. It is a matter of trust in the product. I didn’t have any particular problems before, but this case is fundamental.
Hello @eeigor! Problems may be related to the “Winners” name, which refers (in reverse cell order) to a range that has merged cells.
@sokol92, however, there are no errors when opening the file. Even when editing cells above merged cells Calc crashes. It looks like the problem is not with Calc, but with StarBasic. Merged cells are considered empty (Variant/Empty).
UPDATED:
Worked better with one sheet. Okay, I removed the second sheet. The “Name Box” still contains the range names of the deleted sheet, but the “Manage Names” dialog box has been updated. Range names mismatch.
It started to work a little better, but not much. Moreover, you can make the same correction, and Calc crashes, then no.
You can check.
- Remove formulas containing UDF functions from cells
- Delete rows containing merged cells
- Write down the formulas (one at a time).
Are there crashes?
@sokol92, yes, it crashes. I removed the second sheet, removed the rows with holidays and merged cells, respectively. There is no stability. I removed all range names. Did not help.
Judge for yourself.
players-and-points (castrated).ods (22.5 KB)
But when you open the file or recalculate all the formulas, everything is fine.
If someone rewrote this in Python, then my guess about the failure of StarBasic would become more solid.
Of course, I have no complaints, and the task is not mine. But I’m disappointed…
Let’s keep the example down to a minimum.
- Delete rows 5-17 and 2 in the last document.
- Remove conditional formatting.
What steps do you need to take to get a crash? I am unable to get it.
Maybe you should reboot your system?
Removed. The initial file works correctly.
Conditional formatting was suggested by @Lupp and it came in handy. It’s a pity to lose this, but perhaps the author will be satisfied. I wrote to him.
Row 2 cannot be deleted because cell $B$2 is fixed.
Is there something wrong with the conditional formatting? But I don’t know how to describe this bug, because the example looks difficult to demonstrate. Help.
I can’t yet, I have no crashes. 
players-and-points (3).ods (33.9 KB)
The initial file w/o conditional formatting works fine. I use Linux.
And then this error is not StarBasic one, but Calc.
Try to change condition in conditional rule to formula D3=$A3.
Did not help. I left one coherent range with the conditional formatting applied and it crashes Calc and the other cells are editable well.
I only did very limited testing with (2) and didn’t look yet into (3) yet.
There were no crashes, but the solution is not correct (disambiguation, exclusion of previos winners).
On the other hand I had lots of crashes using Basic / IDE with versions of the 7.x series (under Win 10).
@Lupp, previous winners are eliminated if the number of players is greater than or equal to the number of games played. I made a reservation.
The author was satisfied with this decision. The problem was with the crash of the application.
Perhaps this bug manifested itself in Linux. @sokol92 also had no errors (Windows?).
Corrected one reference to a range in the original file.
That makes the question moot though because the crash doesn’t occur any more with the now current sample file, apparently not even if iterations are enabled again. Anyway, bug fix under way with https://gerrit.libreoffice.org/c/core/+/122949
@erAck, this file from another forum causes the error in question. You can test it in a new build. Enter 99 in cell D3. Crash!
ex-player-values (2).ods (29.2 KB)
The document unnecessarily has iterations enabled, there are no circular references. But somehow in this document it may lead to a crash deep in the iteration resolver, might be that view update kicks in while formula cells are recalculated and it tries to access a currently being recalculated cell and then triggers an iteration recursion.
However, disabling iterations under Tools → Options → Calc → Calculate, Iterative References, uncheck Iterations, a crash wasn’t reproducible anymore.
2 Likes
@erAck, well, I knew who to turn to. Works. Great.
However, I do not know whether it is necessary to refine something in Calc in light of this. You know better.
Yes of course it’s a bug.
A subtle mistake. But confidence in the product has been restored.