Latest official Archon (1.5a) doesn't work on latest OpenOffice or LibreOffice

Just to see if the situation had improved, I downloaded a fresh copy of the latest Archon (1.5a) off this website, and installed the latest versions of OpenOffice and LibreOffice on my Macbook, and took a look at how Archon worked on both of them.

Result: broken on both.

OpenOffice: the "Tournament Info" row for "number of rounds" presents you with a dropdown for tournament type instead, making it impossible to enter the number of rounds. There may be other bugs, but that's the showstopper I ran into first.

LibreOffce: most calculations ralating to table seating and standings fail, with "##" in the column and an error message.

The old Archon (1.3) seems to work on OpenOffice, but fails in some way (don't remember exact problem) with LibreOffice.

So, we have a situation in which a new tournament organizer is pretty much screwed. If he/she downloads the official Archon from this site and tries to use it with current versions of either of the popular Office replacements, things will fail. Possibly so that the problem is only noticed during tournament setup, leading to angst.

Does anyone have the time and the spreadsheet knowhow to fix the situation? This is very annoying, even to us old organizers: apparently the combo of 1.3 + OpenOffice is the only one that currently works, with latest office software versions.

Of course, if *could* just be an OS X thing, but I doubt it, I remember running into serious problems on Linux also some time back.

Didn't we have this problem during the December tournament in Helsinki.

Luckily I was able to use an older Archon file to make things work but it caused some unnecessary delay. And plenty of frustration and unnecessary crankiness.

Ok, looking more into this.

On latest OpenOffice (4.0.1) on Linux, it's actually quite easy to get things to work. The problem there is that cell $B$11 on "Tournament Info" is linked to the "Tournament Format" dropdown for some weird reason (it isn't linked to it when opened with LibreOffice, for example, which is bizarre).

Fix: go to that cell, choose "Data > Validity" from menu, and set the data range to numerical. After than you can set the number of rounds, and things work.

Why does this happen specifically on OpenOffice? No idea.

On latest LibreOffice (4.2.0.4) on Linux, a different problem, and less obvious on how to fix. Here, the above bug does not manifest, the "Number of rounds" cell is a normal one and not a dropdown.

However, the calculations which do the table seating (etc) are failing. All "Round N" sheets have just "##" in the "#" column (and no player names), and if a "#" cell is selected the error message "Error: not a valid reference" appears.

The problem seems to be in this calculation:

=IF(ISBLANK($'Tournament Info'.$B$11),"",INDIRECT("'Optimal Seating "&$'Tournament Info'.$B$11-1&"R+F'!A7"))

(taken from first row of Round 1 sheet)

Apparently that INDIRECT calculation is not valid in LibreOffice, but I have no idea what's wrong with it. My spreadsheet-fu is limited

Ok, some more investigation.

the formula inside that INDIRECT results in stuff like this:

'Optimal Seating 3R+F'!A7

Which seems to be something called a "3-D reference", which is used to do calculations across a set of sheets. However, here there is only one sheet, so I have no idea why a 3D ref is used. In any case, that reference is not valid (for some reason) in LibreOffice 4.2, even though it apparently is ok in OpenOffice 4.0.2 (and Excel, I presume).

If I change that reference to form

'Optimal Seating 3R+F'.A7

(i.e. ! -> .)

things start to work. Wheee! So now to see if I can generate a new version of 1.5 which actuall works in OpenOffice / LibreOffice.

I still have no idea of how this stuff actually works, I have close to zero experience with Excel formulas. But hey, Google.

Now to figure out how to change 200-ish lines of functions (per page), replacing ! with .

Don't intend to do it manually...

Added: ah, find+replace. Too simple

Sigh, I can apparently make the changes, but actually saving the modified version in OpenOffice or LibreOffice (in .xls format) introduces various weird display bugs. Sigh.

Guess I'll have to try to dig up a copy of Excel to do the changes, assuming I find one.