Automated 40K Crusade Roster (2024)

First thing's first: click this link to go to the sheet, or click the image below.

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!

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.

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.