Skip to main content

Automated 40K Crusade Roster

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.

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, but honestly, I think it's pretty useful for anyone who regularly changes their list between games.

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 are then sheets for each unit to store information like their experience, abilities, combat tallies, relics, 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.

The sheet displays both Power Level and Points, so you can use whichever you prefer. It doesn't matter either way - it's just as quick to add up either one.

Why did you make this?
Because I'm unaware of any resource that does this in a phone-friendly manner. Arguably it would be better as an app, but at least with Google Sheets this means you can change it to your liking. 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.

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 either of the images below.

There is a Readme sheet that provides instructions, but I'll repeat them here:

1. Go to File and make a copy of the Google Sheet on your own Google Drive. It's probably best to do this, and 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. Enter the appropriate information on the sheet for each unit. When you enter a number in a unit's Points, Power Level or Crusade Points, this will automatically update in the Order of Battle sheet. It might also be worth deleting any lines you don't need - most units don't have psychic powers, for example!

3. Prior to a game, click/press the tick boxes in the Order of Battle sheet to quickly add up your Points, Power Level and Crusade Points.

4. Use your phone to update your units' tallies during your games.

Customising the tab names & troubleshooting

Changing tab/sheet names
If you want to change the name of the tabs from Unit 1, 2, 3 etc, go ahead . After you have renamed them, you will also need to update the appropriate Crusade Card/Sheet Name field in the Order of Battle sheet. Google Sheets is 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 get a #REF error in the formulae, delete the sheet name on the Order of Battle. Then, enter the sheet name again - that should force a refresh and clear the problem.

Adding/removing unit sheets
If you delete any unit sheets, you'll want to delete its name from the Order of Battle sheet. Likewise if you need to add sheets for new units, you'll need to enter the new sheet name in the Order of Battle.

The easiest way to add a new unit is to right click on a unit card and duplicate it. 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, the 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.

Adding images for your 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 filsize, 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.

Since I'm a rampant obsessive, I have created a simple Photoshop file that's 1000x500 pixels. If you dump the photo you want to use into that file as a new layer, and use the transform tool (CTRL+T) to move it about and crop/resize to your liking, then save it as a JPG or whatever, this will ensure all your images will display with the same proportions inside the roster, which makes browsing between different unit cards less disorientating. Obviously you'll need to adjust the row height in the sheet when you first add the image; I didn't want to put a massive empty box on everyone's sheet just because some people might want to add images. Here are some examples:

If you want said template, go here. Of course you could also chuck in an army symbol or whatever else on your Order of Battle, as I've done here for my Cobalt Scions.

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.

Final Thoughts & Feedback

Hopefully this is of use; obviously you'll still need to manually enter all your units' points and power levels in their unit cards, and update those whenever GW put out any changes, but after that initial setup I hope you'll find this easier than the many clicks needed to create a list in Battlescribe.

I've chosen not to try and get this spreadsheet to contain all your units' rules and stats - this is just a Crusade Roster. Personally I find Battlescribe's output a bit visually bloaty, and the 40K app isn't yet ready to be an in-game thing, so I've made my own summary sheet for my Space Marines. I'll put that out in a future post after the new Codex drops.

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!


  1. Thank you Charlie, I will try it out and be back with feedback - if I got any ;)

  2. Doesn't seem to work. Trying to tick the boxes just gives an option to copy

    1. How strange, no-one else has reported having that issue. Which platform are you using (e.g. smartphone touchscreen, PC, etc)?

      I 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 :)

  3. Super useful, thanks for sharing!

  4. All I get is the read me sheet? Am I supposed to just build my own sheet or what?

    1. If 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.

      You 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.

  5. I cant seem to interact with it at all. It is tagged as a read only file.

    1. It 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.

  6. This is a great tool! Have shared with my gaming group, and will be trying it out in our current crusade.

    1. Fantastic, 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

  7. This has been a great tool! Thanks so much for preparing! I have made a change for my copy.
    I 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.

    1. Should clarify: Delta is positive or negative. Positive if earned RP, negative if spent.

    2. Hi 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.

  8. Hi 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?

    1. Hello! 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.

      Does that make sense?

  9. please, use app for android

    1. Very cool! I personally like having the freedom to customise things as I please, but this does look good in a number of ways :)


Post a comment