Magus' Excel 2007 Initiative Tracker DM Tool

Hey everyone,


I’d like to share something I whipped up in Excel 2007 to help track initiative in combat and keep track of PC defenses, skill mods, and ability mods. It has two sheets, one for the initiative table (“InitTable”) and one as a roster of combatants (“Combatants”). A macro (called RollInit) rolls d20, adds the mods, breaks most ties, and sorts in initiative order. I have assigned a hotkey and a button for the macro, so in a game I just copy and paste rows from “Combatants” into the table in “InitTable”, hit a button, and combat starts. My players don’t mind losing out on physically rolling their initiative in exchange for letting us go from suspense immediately to fighting with no bookkeeping delay—very nice.



Technical Notes: I did this in Excel 2007 and it uses a macro. I saved a copy in the older (Excel 97-2003) format but have no idea whether/how it works. I also know that macros can be a pain to allow. In case you have issues enabling my macro but know how to create your own I have included the code as a text file so that you can create an empty macro and paste it in so that it’s “native” to your system (not that I can guarantee that will give it permission to work, either). I assigned a hotkey combo to the macro and created a button in the QuickAccess section for it, but that sort of thing is not part of a macro itself but part of your Excel settings, so you’ll need to do that yourself if you like (I recommend it). Please note that there is a free Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 file formats available. I mention this in case you are using an older version of Excel, find that the old file version I saved doesn’t work, and want to see if the compatibility pack will let the new file version work for you (not that I know if it will). I do not know whether/how this can be made to work in Open Office and have no plans to try but if anyone else wants to please feel free to share. I am not a programmer/developer and made this for my own use and want to share. I don’t have the skills to turn this into something extravagant and think I’m pretty much done with it but if you have any suggestions/comments please share and if anyone wants to use this as a starting point for something be my guest.

How it works:

SHEET 1 is called "InitTable". There is a table object currently spanning A1:AG5 where you see nice formatting. I don’t know if this is the same outside of Excel 2007. Alternating row colors are preserved even if you insert, delete, or sort rows. You can insert rows/columns between existing ones or you can expand the table by click-dragging the right-angle symbol at the bottom right of the table. Here’s a tour of the columns and how they work:

Order. No formula. Final initiative calculations are pasted here as values by the macro. Values can be manually added/altered mid-encounter (e.g. due to ready/delay). Decimals are part of the tie-breaking solution (see below) and I show 2 decimal places in case I need to enter someone manually and need that level of precision to know where they should go. You can click someone’s Order cell to see the full number, and you can change the column’s formatting to show just integers or more/fewer decimals without changing the actual number.

Combatant. I use green for PCs, navy for allies, red for enemies.

AC, Fort, Ref, Will. Self-explanatory. Does not impact initiative but I like having this info readily available.

InitTot, d20, InitMsc, InitMod. The action happens here. For InitMod manually enter the standard modifier (usually Dex mod + half level. For InitMsc manually enter the total situational mods (e.g. my players usually have +2 for having their Warlord nearby). d20 generates integers between 1 and 20. InitTot adds the other ones up and breaks most ties by adding two tiny fractions: one based on the sum of InitMsc and InitMod so that ties go to the combatant with a larger standard+situational modifier to the d20 roll, and one based on a very small random number, to break ties if even the combatants’ total modifier is also the same. These additions are so small that they should not affect the order unless there is a tie to begin with. You may wish to write-protect the InitMod and d20 columns since you’re not supposed to enter anything manually into those cells. Note that in Excel lots of things you do will cause random numbers to be recalculated, which could wreak havoc once you have your order determined. Therefore, the macro copies the numbers from InitTot and then pastes them as simple values into the Order column. Recalculation of formulae thus doesn’t affect the Order column. Use the Order column, NOT the InitTot column, to manually change initiative during combat.

Skill and Ability Mods. Like defenses, these are here for convenience and have no bearing on initiative calculations. I bolded PCs mods for trained skills as a visual cue. I colored Insight and Perception differently because they are so commonly used. I didn’t create passive check columns since I prefer to save space and just mentally add 10. Note also that a lot of this info is missing for non-PCs. That’s because I don’t use this to track everything in combat, just initiative; I use monster cards instead, so I only add what I need/want for them, but you can add whatever you like.

Adding/deleting columns. You can add/delete columns after the Initiative-calculating ones and that should not affect the macro, but I don’t know whether doing so before those columns screws it up by making InitTot, d20, InitMsc, InitMod something other than columns G through J.

SHEET 2 is called "Combatants". The headers are the same but there is no special table object. You can create your roster here and then copy and paste entire rows from here into the table on the InitTable sheet to put them in a combat. As I said before, you can enter as much or as little info as you like as long as you provide the standard and situational initiative modifiers (InitMod and InitMsc). Rows below are ready to add more creatures. Xs in the InitTot column remind me that these do not have info needed to properly calculate initiative but that the formulae are in place. You see all of those numbers in the d20 column because every time calculations are made those random numbers get generated, but they will be recalculated of course when you use them in the table so don’t worry and certainly don’t delete those cells since they contain the formula to generate d20. Make sure that if you add/delete columns to customize this you do it BOTH on this sheet and the InitTable sheet so that they match properly.

RANDBETWEEN is not a function in Excel 2003. Use RAND()/100 to accomplish the same thing as RANDBETWEEN(1,100)/10000 (technically there's a difference in that this isn't rounded off, but for your use, that's fine). Use TRUNC(20*RAND())+1 to accomplish the same thing as RANDBETWEEN(1,20).

Oh there's also the (minor) problem that 2003 doesn't like your List object and trying to sort that with VBA. I suspect you could do with a named range, not quite as powerful but still works.

infocynic said:

RANDBETWEEN is not a function in Excel 2003. Use RAND()/100 to accomplish the same thing as RANDBETWEEN(1,100)/10000 (technically there's a difference in that this isn't rounded off, but for your use, that's fine). Use TRUNC(20*RAND())+1 to accomplish the same thing as RANDBETWEEN(1,20).

Oh there's also the (minor) problem that 2003 doesn't like your List object and trying to sort that with VBA. I suspect you could do with a named range, not quite as powerful but still works.

Click to expand...

Thanks for checking these. The RAND thing is easy to switch for 2003 users but the list object problem is most unfortunate. For sorting a named range, I assume by that you mean something like A1:AG8. The problem there is you want to be able to add/remove rows (combatants) and still have the macro work, but that range will change based on the number of rows. If I still had easy access to Excel 2003 I'd be happy to check this out because I'll bet it's not hard to make something work. If anyone out there has a solution and is willing to tweak and send the 97-03 .xls file to me I will replace the current file in my zip with it.

I'll look at it later (must prep for dm'ing first! ). I was actually thinking there might be a way to define the range dynamically using index and offset, there's old tricks for that, you might need some magic "STOP HERE" cell.

I made a lot of changes. I also have no use for the 2nd sheet but I left it for you. The list doesn't pull from it or anything, so it seems quite redundant. Just do all the data entry on sheet 1. I used conditional formatting for PC/NPC/Enemy, allowed you to specify Warlord true/false for each, and took out InitMod (uses DexMod + InitMisc + 2 if warlord ; Dexmod must include + half level and there's a note reminding users -- that's how the enemies will be listed in MM anyway). InitMisc is now just for Item or circumstance bonuses/penalties.

I tried messing with sheet protection but it was a pain with the VBA so just don't edit cells with formulas, and don't put anything in column b below the table (blacked out for a reason!): that's part of the magic to be able to detect the edge of the table).

Thanks for doing this. Don't know if anyone else has interest in the file, regardless of version, but I'm enough of geek to enjoy this for its own sake. So I opened the file in Excel 2007 and it seems to work fine as expected. A question I have is, looking at the macro, how did you specify the area to sort? The relevant code is:

Range("Combatants").Sort _
Key1:=Columns("A"), _
Order1:=xlDescending, _
Header:=xlTrue

Where does this name "Combatants" get used to specify the range? There's the second sheet called Combatants but it's sorting (correctly) on the first sheet. Even the column "Combatant" lacks the "s" so I'm curious. My guess, but please tell if you know, is that since you can always right-click on A1 and from there get it to sort (at least in 2007 you can), that saying "Key1:=Columns("A") in the macro likewise makes Excel "assume" you want to sort a table consisting of all rows up until where there is a blank one.

Regarding InitMod, the reason I made it manual and not autocalculating is because I don't really use the spreadsheet to store all the info on monsters, and sometimes on the fly I just want to throw in the monster's name and initmod without having to specify it's Dex mod. Also, not all monsters have an InitMod = Dex mod + 1/2 level. Certain types (e.g. Soldier, Lurker, Skirmisher) have an additional +2 or +4 by default and I assume some monsters just break the mold regardless. For the Warlord status, right now you have it as PCs always have the warlord bonus and others never do, when sometimes PCs won't have that benefit and sometimes friendly NPCs will.

You're right that the second sheet (Combatants) is kind of redundant. At one time I just put the roster of combatants on the same sheet but well below the formatted sorting table. I forget why by at this point I think it's better not to have to replicate tweaks across both sheets.

Thanks again for this! Again, if you can confirm or correct my thinking on how that macro works I'd appreciate it.

Excel allows you to name ranges (and by definition a single cell is a range). I don't have o2007 on my work PC so you'll have to dig in the ribbon for it, but in o2003 it's Insert -> Name -> Define (ALT-I, N, D may work). I vaguely recall it being on the data tab of the ribbon.

Basically you define a range either by a fixed absolute or relative reference (handy if say you're making a character sheet, you could have a cell that calculates your STR mod and name it STRMOD). Any formula or VBA function can access that range, and if it resolves to a single cell, then you just use it like you would $B$25 or wherever your strmod is.

This definition is a little more interesting, in that it uses OFFSET and COUNTA to find the boundary of the table. The width of the table is found by looking across row 1 for all nonblanks (if you have a blank interposing, this won't work, which is a minor problem but workable, and I could fix it but I'm lazy )

The height of the table .. well, I needed something reliable to count, so I used PC names (column b). Again, I was lazy / sloppy and only did a counta instead of a match(*) but oh well. So that way the range is always dynamically checked, if you add a PC, as long as they have a name, you're golden, and you can add more columns as long as they have a header (hard not to in a list).

DM Magic said:

I am very interested in this! I currently use InitTool, but would like something less Java based and more 4th Edition based.

Any new versions?

Click to expand...

Any particular requests? I don't use it to track conditions and all sorts of info like I think InitTool does (?) because I find it faster to do that with pencil, paper, tokens, etc. I pretty much use this for Initiative and for defense and skill stats on the players. If you have any suggestions I'm happy to consider them. Have you checked out the Excel 2007 version (.xlsx) or the older version (.xls)? Does the macro work okay for you?

This tracker is pretty neat. If you're looking for something that will track effects/marks/etc on top of tracking initiatives that can change, as well as allow you to build encounter groups that you can add at will, you might want to check out my Encounter Manager tool (it's a standalone application, not based on Excel, as well).