Nothing but problems with the List Builder in Libre Office

[Plantagenet]

Much as I like the rules I must admit, the number one thing I dislike about MeG is the need to use an Army Builder which is basically a spreadsheet designed in and for Excel.  For people not running Windows that straight away causes potential problems with folks forced to use something like the garbage that is Libre Office.  I'm not sure if a Numbers version of the spreadsheet for Macs is possible or whether that programme is too limited to run the required formulas.  I'll take a look at some stage as it would be a definite improvement over Libre!

1) Compared to other rules with simpler army lists I do find the entry process really a bit of a drag in all honesty.  Multiple units with multiple drop down box selection across a wide screen isn't a very positive experience, for me at least.

2) The first problem with not using Excel, at least currently, is constantly being asked for a password even if you just leave the sheet open without typing anything in Libre.  This is with a fresh version of the current sheet.  The password is included on the Instructions page but no matter what I do it keeps telling me to re-type the password and the bloody box will not go away.  Typing the password doesn't release it and clicking Cancel doesn't either. I type the password in, type it again, the OK box changes to blue and back to the bloody 're-type the password, box.  The only way I find I can stop this infuriating behaviour is to unprotect the sheet and save it before getting stuck in the 'sorry you wasted half an hour filling this crap in, now start again' loop. Honestly this could not be designed to be more user unfriendly!  The amount of times I nearly slung the laptop up the wall because of this I've lost count.

3) Folks are told to Save early and often but when saving with the current version of Libre (7.0.0.3) another issue arises.   It will warn you to save in ODS format and that you may get problems if you try to save as XLSX.  No mention anywhere of what to do about that in the 'Read these instructions' page?  We are left to guess which option to select.

4) Getting that 'Display' box to actually work on the Print page by unticking 0 never works! I uncheck '0' from the dropdown and promptly, nothing happens.  It doesn't stay unchecked and it doesn't affect the sheet at all!  In fact the 'instructions' on what to do bear absolutely no resemblance WHATSOEVER to the options you currently get in Libre Office which are

Sort Ascending
Sort Descending
Top 10
Empty
Not Empty
a search box
and then the checkboxes with 1 and 0

What actually DOES work, and which I only found by multiple trial and error attempts, is selecting 'Top 10'.  So the CORRECT instructions to get only the cells with detail in to display for the current version of Libre are:

'Top 10' - to get it to display just the rows with data entered

or

'Not Empty' - to get it to revert to displaying all the rows.

Ignore the check boxes with numbers in which seem to do nothing.

Not a positive experience overall TBH.  Were I playing a lot of armies I'd find having to complete this spreadsheet multiple times a real drag, it might work fine in Excel but not everyone has Excel and these gash programmes like Libre are often second rate at best.  Few people will change their machine and OS just so they can play MeG I suspect.   For me, I find the problems of using this spread sheet a real time consuming drag.

[nikgaukroger]

I assume this is using Libre Office on a Mac? Having played around with the Windows version I have had no issues with it at all which makes me wonder if there is something about the Mac version that is causing the problems people are reporting. Might need a version creating in the Libre Office ODF format to solve this perhaps?

BTW I don't believe you can run the army builder on Numbers - I recall somebody having issues when they tried and then moved onto Libre Offcie for the Mac.

[SteveO]

I am an Apple user and use Google Sheets, which is free. For the most part, it works ok. The only issue I keep running into is when I go back to an old list I have used and try to amend it.  Sometimes I cannot get the changes to roll through to the print page (or scale the printout).

I agree having the lists separate to the calculator is a bit of a bugger and I would love to see them combined. However, I understand there are some challenges to doing so and, as I cannot fix the problem myself, I just accept it as the 'cost of doing business'.

[Plantagenet]

OK, further update on this.  It looks like this sheet might perform differently on Libre for Mac than Libre for Windows. Personally I'm not fond of suites like Libre Office and while having the problems which are causing the endless "Enter Password" loop fixed would help, IMO having a Numbers for Mac version would be preferable and would at least cater then to the two primary OS out there without having to resort to the use of junk-ware or web based applications with dodgy privacy issues.

In terms of the Password loop, I have no idea what is causing that at all.  I am running Libre for Mac 7.0.0.3 (the current version) on a Mac using OS Catalina 10.15.6.  Whatever it is though, typing in the password doesn't get rid of it.  I have now saved a version of the original xlsx from the site and named it as Unprotected and will use that exclusively to get round the password glitch until I find a better (software) solution.

As to the problems of trying to get the sheet to work on the Mac with Libre:

1) When trying Save, you will see a Warning "This document may contain formatting or content that cannot be saved in the currently selected File format Excel 2007-365".  It will then tell you to save in ODF.  It is not clear at all what folks should do.  Also, I'm not sure whether Excel 2007-365 is the general excel format or whether it's a subscription service version that may be causing further content problems.  The Excel 2007-365 is the version that downloads from the Meg Website.  It looks though that that is now the default label for xlsx files and shouldn't really be causing problems.  I suspect it's Libre for Mac, or at least, the 7.0.0.3 version of it.  I may try downloading an older version of Libre later to see if it fixes it. I dislike using this junk ware at the best of times though so I'd rather be rid of it than trying different versions.

2) At some stage you will encounter the 'enter password' loop, especially when trying to save or print the document given it is protected.  The problem is the box will pop up but even entering the password will not make the box go away and free up the sheet, essentially, nothing will.

I will try Google Sheets later and see if that is any less of a disaster (see post below - it isn't) but as it stands the current situation is less than ideal.  If people are going to be forced to use a spreadsheet to create something as basic as an army list I think we should at least try to ensure that it's a spreadsheet that is compatible with the two main operating systems out there.  Libre Office clearly has its problems and not everyone is a fan of using anything Google at all with their privacy issues.  As such it could be a barrier to some folks adopting the rule set seeing as they can't even write a list without the spreadsheet.

In that regard I have started looking to see whether the sheet can be ported to Numbers, the Mac spreadsheet app.  Mostly Macs will open Excel sheets fine without loss of functionality or display issues.  In the case of this sheet there appears to be two problems:

1)  The TUGs to break calculation is using an old Excel formula of SUMPRODUCT which was around before SUMIF which replaced it.  As such, current software versions do not support that use of SUMPRODUCT, only Excel based sheets which do so to support legacy sheets folks may have.  The cell in question (P6) uses this formula:

INT((SUMPRODUCT((A14:A47≠"")÷COUNTIF(A14:A47,A14:A47&""))+1)÷2)

and carries this warning:

The range A14:A47 can't be used as a single value

The Print sheet also has a problem in that it can't display TUGs to break but I have a feeling that will fix itself if the correct formula can be found to fix the above.

I am now looking to see if SUMIF can replace SUMPRODUCT and how the syntax needs to change, if at all, to work in Numbers, allowing us to have a Mac version.

2)  The Print sheet has the 'Display' box which is supposed to show only rows with entries in.  As above, the current instructions for Libre on Mac are incorrect but I have already outlined the correct selections to make above.  What I am trying to find is a way in Numbers to display only populated rows.


Jeez, all this to write a bloody army list and "it works for me" doesn't solve the problem for those for whom it doesn't.

[Plantagenet]

As an aside, I have created my own memo for using the list builder on a Mac in case I should forget what I need to do to get it to work!:


Instructions correct as at: 24.09.2020

The MeG Army Builder is an Excel spreadsheet using the xlsx file format.  Some formula used in Excel are not compatible with Numbers for Mac.  Users are therefore advised to use 'Libre Office' or Google Sheets.  The sheet is not compatible with 'Open Office'.  When downloading Libre Office be sure to use the latest stable build which may not be the first (and likely Beta) option offered.

If using (Libre Office 7.0.0.3) on Mac (10.5.6 or later)

Creating a list:
Download the latest Army Builder xlsx sheet from the MeG site
Check if both Input and Print Sheets are currently 'Protected'
If they are, click Protect to remove the Protect checkmark (this may require the password 'm3g').  This has the undesirable effect of removing protection from the sheet which allows it to be corrupted by user error but removes the annoying pop up telling you to input the password on some version of Libre.
Save the sheet in ODS format (this is because Excel sheets contain some formulas that Libre Calc cannot interpret or use).
Create army list as normal being sure only to type in the White boxes on the now unprotected sheet (use the drop down menus for everything else).
Do not use the Insert Rows option to add new rows between units (not sure what's going on here but while using Insert Row LOOKS like it works it causes no end of problems with the list on the print page and throws the unit names out of sync - so for now I just don't do it, a pain when you forget a troop type though).

Printing a list:
Open the Print sheet
Go to the sell marked Display with the drop down menu
The instructions on the Meg site and in this sheet are WRONG for Libre Office 7.0.0.3
If using, or if the 'Deselect 0' option doesn't work select 'Top 10' from the drop down menu.  This will display only the rows with entries.
To revert and show all the rows select Not Empty from the menu.

Sharing a list:
The ODS file format is not proprietary and should be recognised by most applications.
Not all applications (e.g. Numbers for Mac) may support the formulas used in either the ODS or xlsx versions of the file.
It is also possible to save your finished ODS file in the the xlsx format using the Save As option to ensure full compatibility for users of Microsoft applications.

[Plantagenet]

Not really related but while we're looking at options.  I tried Google Sheets too, not keen on that solution myself anyway given that it's a) web based and b) Google.

Looks to open up OK but with all the lag associated with web based applications but on the Print sheet the Display drop down doesn't work at all.  Brings up a few options plus a load of garbled icons with nothing that you can do to bring up the sheet with only the populated rows.  Not an option I will be considering.

Having to expend this much time and effort JUST to make any army list, there is something seriously wrong somewhere.  Were I not prepared to persevere more I'd be inclined to scrap the rule set because of it TBH, life's too short.  Folks new to the system who decide to try out the builder before buying the rules may well be tempted to pull the plug there and then rather than put up with the hassle.

[attachment deleted by admin]

[Plantagenet]

One other issue I found with the Spreadsheet and I'm not sure whether this is common across all versions but what do you do if you forget to include a unit that, in order to have the sheet print out in some semblance of order, you want to insert between other units in the list?

It looks like merely inserting a row, which would be the option on any normal and simple spreadsheet, doesn't work here.  When you do insert a row all of the boxes populate making you think it's working UNTIL that is you get to the number of bases and look for it to calculate the points cost per unit and total.  Again in a simple sheet, merely copying a SUM function from a cell above or below will transpose the correct cell numbers for the row in question, allowing the formula to work correctly.  Not so here, copying the contents from one of the other content cells merely creates yet more problems. 

As such it seems the only solution seems to be to add the forgotten unit to the end of the list (which will produce a none too organised print out) or, start again!

[lionheartrjc]

One other issue I found with the Spreadsheet and I'm not sure whether this is common across all versions but what do you do if you forget to include a unit that, in order to have the sheet print out in some semblance of order, you want to insert between other units in the list?

It looks like merely inserting a row, which would be the option on any normal and simple spreadsheet, doesn't work here.  When you do insert a row all of the boxes populate making you think it's working UNTIL that is you get to the number of bases and look for it to calculate the points cost per unit and total.  Again in a simple sheet, merely copying a SUM function from a cell above or below will transpose the correct cell numbers for the row in question, allowing the formula to work correctly.  Not so here, copying the contents from one of the other content cells merely creates yet more problems. 

As such it seems the only solution seems to be to add the forgotten unit to the end of the list (which will produce a none too organised print out) or, start again!

If you need to insert a row, my recommendation is to add the row after the other entries, put the TuG numbers into the order you want; select the cells from the name to tthe number of bases and then use the Sort option to sort the cells into the order you want.  DO NOT insert rows.

Richard

[lionheartrjc]

If anyone is having problems with the LibreOffice, they are welcome to send me a message, include their email address and then I'll send you an ODF version of the spreadsheet.  It might avoid some problems and I'd like it tested out.

Richard

[Plantagenet]

Is the ODF file format the only that has changed with the version you have?  I managed to save an ODF version here which is still in its 'Protected' form.  I'm currently testing a few trial sheets of it to see if the password prompt / loop gets triggered (either by just leaving it without inputting anything, trying to save it again or by typing in rubbish into a 'user' fillable square).  Currently no problems so it would SEEM that the ODF version would solve the issue for use with Libre Office on a Mac.  Indeed users are actually advised, if you do some digging around, to save and work on Excel sheets using the ODF format and then Export them back to Excel for sharing with Microsoft programmes.  This is because some of the functionality of Libre CALC works differently (or not at all) with Excel.  It shouldn't really be necessary to export to Excel again for sharing either given the whole idea is for ODF to be a non-proprietary open file format, but again, user that need to continue to work on the sheet in Excel may find it preferable to export first.  In addition, some business users may find company firewalls may block the receipt of files which are not in Office format.

On the subject of Numbers for Mac, the formula that is needed for cell P6 on the input sheet is looking to be possible (albeit with different commands and syntax) BUT the problem arises on the Print page which, due to the use of Merge Cells in the sheet, renders the Filter menu in numbers inaccessible.  I'm still looking for a work around for that but it may not be possible.

[lionheartrjc]

I created the ODF version from Excel.  I have had a friend test it out and he hasn't encountered a problem with using it.

Richard

[Plantagenet]

Yes, as I posted before, I've had no problems with with an ODF version which is simple enough to save yourself from whatever version you use.  Took way too many wasted hours to get it working though.  So much for modernisation!  At least it seems the points will be published with the lists at the next revision, a welcome development meaning that those who don't want to use a spreadshee at all to create an army list will have another option.

[shaun]

Hi All when will the new version of the lists be done ? are we talking days weeks or months  for a rough guide regards Shaun

[nikgaukroger]

I'd guess around the end of the year. There is an annual review about this time of the year and updated lists released late Dec/early Jan; so incorporating the points would logically fit into that.

[lionheartrjc]

The 2021 Lists will include points.  It involves regenerating all the lists from the master database.  We are also planning to issue lists sets for Magna and Pacto.  This is a considerable amount of work (with 630 lists and 3 sets - even if every list were to take only 3 minutes - then we are looking as 94 1/2 hours work!).  I would hope to get all the Maximus lists and Classical sets for Magna and Pacto ready for the end of December.  The remaining Magna and Pacto lists will follow in the new year.

Richard