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.
Finnish Politics!
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.
Can't we just get archon in modern, open format ?
Always when you use shit m$ products, problems arise.
Sabbat.Black Hand Shakar: Lech loathe ranged weapons. Once each action, he may burn 1 blood to become Camarilla Prince of Krakow until the end of the action.
Well, it's not just an MS problem, I'm also getting weird issues even if I save and work in .ods format; OpenOffice and LibreOffice have compaibility issues with each other (at least on this level, since Archon uses some non-trivial function stuff). And of course, Excel is different in yet other ways. Sigh.
Ok, I managed to create a new .xls version (1.5b) of the Archon which now seems to work with the latest OpenOffice and LibreOffice (at least on Linux). The problem is, I have no idea of whether it works in other environments and/or Excel itself -- I don't have access to a copy at the moment.
You can download a copy from
www.orava.org/vekn/thearchon1.5b.zip
and try it out. Let me know if it works for you
Please do not use this to run a tournament before making sure it actually works on your setup (OS & office software combo)!
For reference, the changes are:
- Fixed the data validation on Tournament Info cell B11 to be numeric 3-4
- Changed the INDIRECT formulas on the Table N sheets to use '.' instead of '!'
- Some tweaks to line heights etc to fix display bugs probably caused by .xls file import/export in OpenOffice
> PetriWessman wrote:
> - Changed the INDIRECT formulas on the Table N sheets to use '.' instead of '!'
>
I've juste tested the archon using Office Plus 2010, this change breaks the archon.
If I change back the =SI(ESTVIDE('Tournament Info'!$B$11);"";INDIRECT("'Optimal Seating "&'Tournament Info'!$B$11-1&"R+F'.A7")) formula to
=SI(ESTVIDE('Tournament Info'!$B$11);"";INDIRECT("'Optimal Seating "&'Tournament Info'!$B$11-1&"R+F'!A7"))
it works again.
A way to make it work on both systems is still to be found.
EDIT: 'SI' stands for 'IF' and 'ESTVIDE' for ISEMPTY'. Thanks you Excel for localizing the formulas
Prince of Paris, France
Ratings Coordinator, Rules Director
See if you can make it work using the ADDRESS function:
www.oooforum.org/forum/viewtopic.phtml?t=85679
Prince of Paris, France
Ratings Coordinator, Rules Director
> Ankha wrote:
> See if you can make it work using the ADDRESS function:
>
> www.oooforum.org/forum/viewtopic.phtml?t=85679
>
Ok, thanks, will have to look into that when I have time.
> Ankha wrote:
> A way to make it work on both systems is still to be found.
>
I think open office should be the priority one since it is free and thus available to everyone
Finnish Politics!
> Lönkka wrote:
>
> > Ankha wrote:
> > A way to make it work on both systems is still to be found.
> >
> I think open office should be the priority one since it is free and thus available to everyone
>
Well, the version I "posted" here (see link) works in current OpenOffice and LibreOffice, so for now that's an interim solution (though, as noted, I haven't extensively tested it yet).
Of course, two separate versions is far from ideal, so I'll try to figure out a solution that also works in Excel as soon as I have time.