I honestly started this quixotic endeavour with the intention of making a simple spreadsheet to track my Crusade tallies. I'd never written an IF function in a spreadsheet before. I didn't even know IF functions were a thing in Google Sheets. Neither had I considered making something that could be used to make army lists in seconds via some tick boxes.
You'll note I've got an extra cell on the Order of Battle sheet shown above. The one where I've put the unit type next to the name. This let me keep the actual sheet names short for easier scrolling on my phone (the sheet names being the first column). I figured it'd be easier for me to parse my ridiculous Roman names if I also had the unit type displayed. If you want the same format, all you have to do is to unmerge the Sheet Name cell, then re-merge the cells as preferred. You can type anything in that second column, as it's not used in any formulae - only Column A is referenced by the formulae, and as long as what you write in Column A is a perfect match for the name of a sheet in the document, the formula will work.
Sometimes, feature creep bears unexpected fruit.
Today's post is intended as a useful resource for anyone using the Crusade Rules in 9th edition 40K (edit: and now 10th edition), but honestly, I think it's pretty useful for anyone who regularly changes their list between games (edit: and who wants a single place for their army/detachment/unit rules).
What is it?
It's a digital version of the army roster for Warhammer 40,000 Crusade. It has been designed to suit tracking your Crusade force on your mobile phone using Google Sheets, so the sheets are formatted to be easily navigable on a small screen rather than a PC. Because it's a Google Sheet, you can update it on either your computer or your phone, and any changes you make will update across those platforms.
There is a sheet called 'Order of Battle' that has various automated cells that calculate things like points, supply usage, crusade points, and so on. There is also a template sheet you can copy and then fill out to store information like individual units' experience, abilities, combat tallies, enhancements, and so on. When you have entered the details for your individual units, you can use the tick boxes on the Order of Battle sheet to quickly tot up the value of the force you want to use for the next game.
Why did you make this?
Originally, because I was unaware of any resource that does this in a phone-friendly manner. Once I'd gotten things like the tick boxes working, it seemed silly to have put all that effort in and then not make it available for other people.
Since then, Goonhammer have acquired and enhanced Administratum, and in many ways it's superior to this roster. But because it's an app, it won't let you customise it to the same extent, and I personally want my roster to serve as a one stop shop to reference all my army, detachment and unit rules. If you're playing in a large, organised campaign and/or would prefer lower effort and higher automation, then Administratum will suit you better. This sheet is for people who prefer higher customisation in exchange for a bit more effort.
Who helped?
I couldn't have made this without the assistance of Tom (occasional Bunker contributor and actual programmer). He pointed me in the direction of the formulas I needed to make this work, and badgered me about keeping the formulas consistent and tidy. Regular Bunker author Andy also made some good suggestions for further refinements, and even embarked on a crazed quest to code some scripts that would automate even more stuff, but ultimately they proved too crunchy for poor ol' Google Sheets.
Thanks also to the other Bunker dwellers, as well as the Independent Characters podcast community who provided excellent feedback.
How do I use it?
First thing's first: click this link to go to the sheet, or click the image below.
There is a Readme sheet that provides instructions, but I'll repeat them here:
PC Users Access
You will not be able to edit the sheet until you go to File > make a copy to create a version of it on your own Google Drive account.
Phone and Tablet Access
Open this sheet, then go to Google Drive and find it in 'shared with me'. Use Drive to make a new copy - you can now edit that!
Initial setup
1. It's probably best to do the other initial setup on a PC rather than your phone. That way it'll be much easier to change the colours, fonts and pretty much anything else you want to change.
2. Once you've formatted the tabs to your satisfaction, duplicate the Unit Sheet Template for each of your units, and fill in their details. Rename the tab with their unit name, e.g. "Squad Gaius".
3. Type each unit sheet name into your Order of Battle. Its points value this will automatically update in the Order of Battle sheet. If you type it incorrectly, the values will instead display as #REF.
4. I strongly recommend that you turn each unit name in your Order of Battle into a hyperlink to each unit's sheet. That way you won't have to scroll through loads of sheets. Use CTRL+K to create a hyperlink, then choose the appropriate sheet name.
5. Prior to a game, click/press the tick boxes in the Order of Battle sheet to quickly add up your Points and Crusade Points.
6. Use your phone to update your units' tallies during your games.
Changing sheet names & troubleshooting
If you rename a unit's sheet, you will also need to update the appropriate Crusade Card/Sheet Name field in the Order of Battle sheet. Google Sheets seems to be less buggy if you rename the unit sheet first, then update the name in the Order of Battle sheet.
If you get the above steps and still get a #REF error in the formulae, delete its sheet name from the Order of Battle. Then, enter the sheet name again - that should force a refresh and clear the problem.
If you end up having more than 20 units, add some rows to the bottom of the Order of Battle sheet, merge whatever cells you need to merge, then extrapolate the formulae down.
(If you're not sure what I mean by that, extrapolation is when you click on the cell with the formula, then mouse over the little square in the bottom right of the cell so that the cursor becomes a +, then click and drag down into the new cell.)
It's also worth noting that if you want to de-clutter your Order of Battle, you can just delete the unit name and that line will go blank.
Spotting and removing the hints & tips
There are various hints and tips throughout the sheet, denoted by a little black triangle in the top right of a cell. If you wish to view a tip, just mouse over the cell. To remove a tip just mouse over it, then drag select and delete the text.
Visually customising your roster & units
One of the gamers in our group, Harvey, suggested it would be cool if there was space to add an image of each unit on its card. Obviously this will bloat your filesize, which is something to consider when accessing it with mobile data, but I've added an empty cell at the top of each tab. There are instructions in that box on how to add an image.
For myself, I made a template in Photoshop and painted a little metallic frame as a template, then took pretty pictures of my units. Restraint is just something that happens to other people.
A selection of the unit icons on my roster (also used in their associated wiki page) |
Here's a screengrab of my own roster. I've chosen to keep the giant supply limit I'd built up through 9th edition, but haven't yet decided which if any battle honours I'll keep - I need my copy of Leviathan to turn up first!
Final Thoughts & Feedback
Hopefully this is of use; obviously you'll still need to manually enter all your units' points in their unit cards, and update those whenever GW put out any changes. As I write this updated version of the post at the dawn of 10th edition, you can copy and paste rules straight from the official PDF indexes (just be sure to paste without formatting; that's CTRL+SHIFT+V in Chrome).
If this roster is beneficial, or something didn't make sense, or if there are changes you'd like to suggest, leave a comment on this post and let me know!
Updates
Version 1_2
23rd October 2020
The unit cards were updated to automatically display the unit's rank based on their total XP. If you want to incorporate this feature into an existing roster, just open the sheet linked above and copy/paste the unit rank formula into your unit cards.
Version 1_3
1st February 2021
Several new features this time, several of which were suggested by extremely helpful reader Jonathan (@jjarcher89 on Instagram). He suggested some changes to the unit card tallies section and the addition of a requisition log.
The Requisition Log
The requisition log was a great idea, so that was a no brainer. It's just so much easier being able to track where your requisition points went, and much easier to figure out if you screwed up. I simplified Jonathan's design somewhat, as I realised this was also a good place to store a minimalist campaign diary... so if you hate it, blame me not him!
Automated unit XP calculation
With the unit tallies, I had faithfully reproduced GW's original design but it turns out this omits things the rules explicitly tell you to track, such as the number of times a unit is marked for greatness. Jonathan made some clever refinements here, separating out the agenda and unit kill tallies, and adding a function to auto-calculate the unit's total XP. This made cunning use of a FLOOR function to ensure that when you divide the total unit kills by 3, the total always rounds down and returns a whole number - meaning it displays the correct amount of XP. What I'm trying to say here is that in technical terms, I invite you to join me in offering Jonathan an approving golf clap.
I've reformatted Jonathan's work somewhat to save visual space and reduce the amount of numbers on screen, but the bulk of the credit is his. I've also added pop-up explanatory notes to various cells; hopefully they're helpful and unobtrusive.
Semi-Automated Agendas
This section edited on 17th April 2021
The final big change I made was to Agendas. I realised it'd be useful if this was more automated, particularly since the Agenda XP calculation is a little laborious since different Agendas provide different amounts of XP, and you don't want to have to copy that information over onto each unit card every time you play a battle.
I've gotten around this by having a new sheet in which you enter your Agendas when you start a battle, and say how much XP each tally is worth. All the unit sheets then automatically update with that information, so all you have to do is update each unit's tally as you play - the sheet will then auto-calculate the total XP the unit gets from Agendas for that battle.
This is far from perfect, however, and needs some caution to use accurately.
If you choose one of the agendas that is less linear, e.g. do the MacGuffin action 3 times to gain 2XP, the sheet won't handle that accurately. It'll still replicate the text and let you enter a tally, but obviously it'll output the wrong number since those agendas use different maths. Therefore, it's probably simplest to keep the tally like normal, then at the end of the game, temporarily delete that agenda's tally so you can see how much XP you got from the other agendas before manually adding in whatever you got for the more complex one.
In theory one could replace all the typing and manual stuff with a drop-down menu of all the agendas in the game, but there's no way I'm keeping that updated as new Codexes come out; the formulas would be relatively complex, particularly for non-linear agendas where you have to do it Y times before getting any XP at all. Better to keep it simple. Well, better for me. Not so much for you.
Optional unit sheet format: full stats & weapon profiles
Following a suggestion from Dark_Goblin on Goonhammer, I have included an optional format of the unit sheet that gives you the space to include literally all the unit's stats, thus meaning you wouldn't need a codex or other summary sheet during your games. This format will probably suit some people more than others, and takes up a bunch more visual space, but it would enable you to incorporate the bonuses from your Battle Honours straight into your stats.
Upgrading your Roster to v1_3
Honestly this update is big enough that it might just be easier to copy the stuff out of your current roster and into a fresh copy. It's a non-mandatory bummer, I know, but hopefully the quality of life improvements are worth it. If it's any consolation, I currently have no ideas for further improvements, so it's not like I'll be putting out another big change in a few weeks.
If you really don't want to have to update your unit cards, you could just export the Requisition Log. To do this, right click on its sheet name and click Copy To > Existing Spreadsheet, and then select your current roster from your Google Drive.
If you have thoughts on these changes, good or bad, please leave a comment so I know what works and what could be improved. Cheers!
Version 2_0
26th June 2023
Cometh the new edition, cometh the update! The original post above has been edited to reflect the 10th edition roster rather than the original 9th edition one as I assume most people will be playing the new edition. The last 9th edition version can be found here if you're still playing that edition.
German Language Version
17th March 2023
As per the comment section below, Florian very kindly made this German language version of the roster.
29th April 2024
And now he's been kind enough to share a German language version of the 10th edition roster.
This is gold, thank you!
ReplyDeleteThanks Danny! I'm glad it's useful :)
DeleteThank you Charlie, I will try it out and be back with feedback - if I got any ;)
ReplyDeleteNice, enjoy! :)
DeleteDoesn't seem to work. Trying to tick the boxes just gives an option to copy
ReplyDeleteHow strange, no-one else has reported having that issue. Which platform are you using (e.g. smartphone touchscreen, PC, etc)?
DeleteI do sometimes find the tick boxes a little janky on touchscreen, since it's both a cell and a tick box, but I have always found that if I just press again right in the middle of the cell the tickbox still works even with the copy message displaying. Let me know if you find the same thing :)
Super useful, thanks for sharing!
ReplyDeleteCheers Matt :)
DeleteAll I get is the read me sheet? Am I supposed to just build my own sheet or what?
ReplyDeleteIf you check at the bottom of the screen you should see other tabs: one called Order of Battle, then others that say Unit 1, Unit 2, Unit 3, etc.
DeleteYou may need to ensure that you have Google Sheets on your device if you're not loading it on a PC, or it will just load the default tab.
I cant seem to interact with it at all. It is tagged as a read only file.
ReplyDeleteIt is indeed read only, since if you edited the master copy, you'd change it for everyone else! You have to copy the sheet onto your own drive before you can make changes. I have emailed you some instructions; equally you can find said instructions in the ReadMe tab on the sheet.
DeleteThis is a great tool! Have shared with my gaming group, and will be trying it out in our current crusade.
ReplyDeleteFantastic, let me know how it goes! I can't wait to actually play some Crusade myself (lockdown woooo), so am always pleased to hear when someone's getting some use out of this bad boy :D
DeleteThis has been a great tool! Thanks so much for preparing! I have made a change for my copy.
ReplyDeleteI added an extra sheet after the "Order of Battle" tab called "Requisition Log". In this sheet, I have logged all Requisition Point-related decisions for clarity and transparency.
The sheet has five columns, named "#", "Battles", "Description", "Delta" and "RP"
The "#" column is simply an enumeration, starting with 0 and incrementing by 1.
The "Battles" column starts at 0 and increments every time you fight a battle. The number in this column is therefore the number of battles you have fought before spending RP.
The "Description" column states what decision you took (e.g. "gave commander warlord trait", "increase squad size by 5", etc)
The "Delta" column is where you include the amount spent.
The "RP" column cells contain a formula that adds the delta column to the cell above it - with the exception of the first cell, which just has value 5.
The first entry in the log is "created order of battle" and has a delta of 5. The RP cell for the first entry is simply the value 5. Every cell after that is the formula mentioned above.
The Requisition Points field in the "Order of Battle" tab I altered to simply be a sum of all the delta entries in the "Requisition Log" tab.
Let me know if you want me to email my copy if my explanation was not clear enough.
Should clarify: Delta is positive or negative. Positive if earned RP, negative if spent.
DeleteHi Jonathan! That sounds extremely cool. I would indeed be curious to see the sheet, so if you're willing, please do send it to cbrassley at the usual gmail suffix. Great idea mate.
DeleteHi Charlie, thanks so much for your hard work! Sorry if this is covered elsewhere, but when I go to add to the EUD field it reads =sum(H16:J18) , not sure where to type the units killed, am I missing something?
ReplyDeleteHello! The EUD field is totaling up the three cells below it, so if you enter your kills as ranged/melee/psychic kills, that cell will automatically keep a running total.
DeleteDoes that make sense?
please, use app for android
ReplyDeletehttps://play.google.com/store/apps/details?id=com.wh40k.recorder
Very cool! I personally like having the freedom to customise things as I please, but this does look good in a number of ways :)
Delete#REF errors everywhere. Appreciate the effort but I'm going to grab a legal pad instead.
ReplyDeleteI'm surprised to hear you say that, as the only time #REF errors tend to occur is when a sheet name is entered imperfectly on the Order of Battle (hence the note that says if you're getting a #REF error to delete the name you just wrote, which forces the formula to refresh, then re-enter the sheet name).
DeleteCould you be more specific about where and when you're getting these errors? It would be helpful to know if this is a hitherto unknown problem with the formulas, or an issue of the instructions being insufficiently clear.
You really out here doing the Emperor's work :,] bless
ReplyDeleteThanks, whoever you are :)
DeleteI do stop REF appearing in the Order of battle crusade cards?
ReplyDeleteDelete the unit name in the order of battle. REF will disappear. Then, re-enter the name. The unit name must be exactly the same as the name of that unit's sheet. If the name is not the same, it will say REF again.
DeleteGreat it works, you’ve done an amazing job. Thank you kindly.
DeleteCheers mate, glad you've got it working :)
DeleteThis is brilliant. I am just starting to dive into this sheet for an upcoming league, but I had a two questions about the agenda in v1_3.
ReplyDelete1) Does each game need new rows for agendas (as in it needs the data permanently for each battle) or does tallying the agenda on the unit sheet save the XP gained and I need to clear the agenda page for my next battle?
2) Knowing only the basics about Google Sheets, how complex would it be for me to go in and add drop downs for my own army to select agendas? I assume this is possible?
Really, really amazing job on this. Thank you so much for your efforts!
Cheers!
DeleteThe agendas sheet is a very imperfect trade-off, and you've confirmed my suspicion that I should edit the post above to offer better guidance, so thank you :)
[goes away, edits post a bit, comes back to comments section]
To answer your questions:
1) You'll want to clear out your agendas each time you play. The unit sheet doesn't save it, since spreadsheets can't save incremental data in that way. The sheet will calculate the XP from agendas during your current battle; you should then manually add that to the "total XP from agendas in all battles" field.
Obviously that's a long way from perfect. The advantage of the agendas tab is that it makes it easier to remember which agendas you picked while you're looking at a unit card, but it won't accurately calculate agendas like "if you perform the MacGuffin action 3 times, gain 2XP." Those, you'll need to do manually.
2) This is possible, but sufficiently complicated that I haven't done it myself. Here are the basics ideas of how you'd have to do it:
You'd need to create an extra sheet and call it something like 'validation.' This is a lookup sheet your agendas tab can reference. You would then write a bunch of agendas in there, then use them to populate a drop-down menu in the Agendas sheet.
This drop-down would of course be very lengthy, and the real kicker would be automating the calculation for each one, since different agendas use different maths.
In the end, I (and my professional web developer friend) both decided it was too complex to warrant trying to hammer it into formulae, mostly because of the agendas that require you to do things a certain number of times before accruing any XP. You could probably use FLOOR and IF functions to wrangle those, but... safe to say it's much simpler to just type in some text and do some mental arithmetic at the end of the game :)
Obviously if people have bright ideas on how to sensibly implement agendas more sensibly then I'm all for it.
Glad you like the sheet, I hope the league is fun!
Understood on Agenda stuff. I thought that might be something Sheets couldn't do but wasn't totally certain if I'd missed something. I'm also just now realizing that boxes with gray fill are boxes that have formulas extrapolated from somewhere else. So the "XP from agendas this battle" calculates from another field, but the "total XP gained" is a free field you can edit and not worry about screwing up a formula!
DeleteThat sounds intense. I may get so into this I give it a shot for my army, but trying to make that work for ALL of them just sounds like insanity. Thanks for the breakdown on it!
My pleasure, I'm glad the visual language is doing its job!
DeleteIf you successfully program a crazy drop down menu I'd be excited to see your implementation when it's done :)
Hi, this looks great. Only issue is the check boxes dont appear for me, instead I just have "True" or "False" and I can only edit the text.
ReplyDeleteThis has only been tested on android mobile at this stage.
Never mind, self-resolved! I was selecting "Save As" rather than "Make a copy" and saving it as a excel spreadsheet rather than a google sheet
DeleteSelf-resolved technical errors are my favourite technical errors :D
DeleteEnjoy! :)
I just want to say my whole crew now uses this and we have an amazing crusade business going! Thank you so much!
ReplyDeleteThanks for this amazing tool! How can I prevent the sheet from dropping experience rank if I lose XP?
ReplyDeleteYou're welcome! I'm afraid there's no way to prevent the rank dropping, but so long as you've recorded your Battle Honours and Crusade Points, those won't be affected. What I tend to do whenever I enter a new battle honour in a unit is to record how they achieved it, since honours can also be awarded as mission bonuses. That way, even if a unit's rank drops below a threshold, it's pretty clear what's going on.
DeleteSo a unit of mine might look like:
SQUAD TYVUS BATTLE HONOURS
- Terminator Honours (blooded)
- Marksman's Honours (mission bonus)
- Centurios Service Studs (battle hardened)
- Enhanced power fist (heroic): fleshbane; +1D.
did the full statline thing from 1-3 get removed at some point? I can't find the option to activate it
ReplyDeleteHi mate, it's still there on the final tab in the sheet - just duplicate it to your heart's content 😊
DeleteHello, I have taken the liberty of translating the Crusade Roster into German. I have also considered the possibility of internationalizing it, allowing users to select their desired language.
ReplyDeletehttps://docs.google.com/spreadsheets/d/1GfdNJEa0GwzULMfh4SB0FEgjumIvL-DIl8dGdFp_V1E/edit?usp=sharing
Amazing! 🤩
DeleteDespite my total inability to read German, I've chosen to trust that you have done solid work and have included a link to your translation in the article above. 🙏
As some german translations are much longer I also needed to change the font-size of some cells.
DeleteI am currently developing this spread-sheet as a Web-App wich can be installed on any device. In the first version I will store the data on the devices itself only.
Hi mate, will this be updated for 10? Removing points and simplifying relics/warlord traits into enhancements?
ReplyDeleteit will, yes. In fact I've basically finished doing the work already, so I'll be sure to post when it's finished!
DeleteAmazing tool, thanks! Would you be able to add a section for secondary unit tallies, like tyrannic war kills? I added one in my own version below the general kill tally.
ReplyDeleteGood idea, thanks! I'll look at implementing that next week when I'm back home 😊
DeleteHi I made an update of the german translation. https://docs.google.com/spreadsheets/d/1eRB2wzbNOnoWYv5sg3yxwCGb1q5BMeT-EAua9F729bE/edit#gid=1121523060
ReplyDeleteExcellent work! We will update the link in the post. Thank you, this is very helpful 🙌
Delete