Here the other day I wanted to make a spreadsheet for a DnD campaign that would automatically generate items but I came across a lot of weird things that made the entire experience incredibly frustrating. So I decided to explain all in a post here.

The Initial problem

So to make my spreadsheet that automatically generated items for DnD I wanted to use the conditional formatting in order to make the cells with the specific item that had been rolled for light up. However I ran into some problems with items that had a probability higher than 1/20. The problem was that there is no function that would look for more than one number, like if I needed to check if the dice had rolled something between 2-5 there was no way to do that. To overcome this I decided to use the logic expressions because I can make an expression will check if the number is either above 2 or below 5. But here I ran into the weird problems.

Google lies about their own app

It would seem rather natural that you can expect Google's own list of functions to be up to date and accurate, but it isn't.

Here is an example function I took from their list. If you look you'll notice it suggests that you separate arguments with commas. This is just wrong, you have to use a semicolon instead, this caused me no small amount of frustration and before I figured this out I instead used a roundabout solution where each argument would be in its own cell and I ended up with a table that looked like an old time computer.

And you'd think that would be the end of it right?

Conditional formatting is incredibly unclear

In Google Sheets you can use conditional formatting in order to change the formatting of a cell or a group of cells depending on criteria you want. Now in order to format one cell based on another you have to use the custom function, with which you can say that a cell will become green if another cell is equal to a specific number. This is what I used but it was a lot of work because of a few things.

1 You can't just copy paste conditional formatting.

If you try to take a group of cells with conditional formatting and then copy them the new cells will just refer back to the old cell. So if I have M4 formatted so it will become green if M3 is equal to 1 then if I copy M4 over to N4, N4 will still become green if M3 is equal to 1 not N3 as you might imagine. And what's worse, Google Sheets will automatically group these two cells into being part of the same rule making it so you can't simply correct N4. This basically means that if you're using conditional formatting for more or less the same thing in two places, say you have two lists of 20 items where you need to roll separately for each list, you're gonna have to manually remake all the conditional formatting you just made.

Luckily there is a way around this but it's strange. What you have to do is open a new sheet, then you take your list copy-paste it into that sheet, then you cut that list from the sheet it's in right now and paste it back in your original sheet. This way you'll avoid having something on the N row refer to the M row.

2 Grouping cells into one rule is counter intuitive

You would think now that when it before would always refer back to the same cell that this would always be the case right? That if you made a group rule then our group of cells in row M would always refer to M3 since before when you copy-pasted a rule it did that. Well it doesn't. Even though if you click on the rule it will say that it's referring to M3 only the first cell, so M4, will actually do that. The following cells will then in secret, with no way for you to see it, refer to incrementing cells. So M5 is actually gonna refer to M4 even though if you click on M5 it will say that it is following the same rule as M4 and referring to M3. Now this is simply overcome by locking the column or the row using a $ sign but there is nothing in Google Sheets that actually tells you that this is the case.

Here you can see how the cell L5 has three rules. One that's specific to it, which makes it light up, and two that a group rules that apply to the entire list. You can see that I have locked the column so that the entire rule will refer only to L24.

Conclusion

In conclusion Google Sheets to be something where you have to a lot of experimentation in order to figure out how to use it. I hope that I have at least been able to help some people by outlining the difficulties I faced and the sometimes round-about solutions I found so that others don't have to deal with the same issues.

• Amber