By Michael G. Barford, Keeper of the Eternal Chime
I’ve been working on a character keeper for Offworlders to demonstrate using Google Sheets for online character keepers. In the Basics post, we ended up with something like this: https://goo.gl/JR1zS1. If you haven’t been following along, you may want to start at the Introduction post.
This sheet has space set aside for most of the information that a player would want to record for their character. You’ll notice, however, that most of that information will have to be entered manually, or at least copied and pasted over from the digital document. Most of the time, when you copy from a PDF, you’ll also encounter a tricky hurdle for spreadsheets: line breaks. When you paste it into your spreadsheet, if you don’t enter it directly into a single cell, it will spill over into the next ones, and if you do enter it into the cell directly (through the formula bar), you will still have to go back and delete those line breaks, which is a hassle!
In this article we will look at some ways to reduce manual input of character info to increase the ease-of-use of the keeper and decrease the length of character creation. Feel free to follow along with your keeper, or start with the basic one I’m working on and go from there!
I’m going to go top-to-bottom while covering these features. The first function I want to cover is data validation. Basically, for our purposes, this allows us to create a drop-down list for folks to use to pick from a list. For example, in our Offworlders keepers, we can list the Classes we want to have available. In my sheet, I have left an open box next to my Class heading. If you right-click a cell, at the very bottom of the list you’ll find the data validation option. Go ahead and click that, and a SCARY INTIMIDATING WINDOW will pop up. Don’t worry! I’ll break it down for you:
I’ve been working on a character keeper for Offworlders to demonstrate using Google Sheets for online character keepers. In the Basics post, we ended up with something like this: https://goo.gl/JR1zS1. If you haven’t been following along, you may want to start at the Introduction post.
This sheet has space set aside for most of the information that a player would want to record for their character. You’ll notice, however, that most of that information will have to be entered manually, or at least copied and pasted over from the digital document. Most of the time, when you copy from a PDF, you’ll also encounter a tricky hurdle for spreadsheets: line breaks. When you paste it into your spreadsheet, if you don’t enter it directly into a single cell, it will spill over into the next ones, and if you do enter it into the cell directly (through the formula bar), you will still have to go back and delete those line breaks, which is a hassle!
In this article we will look at some ways to reduce manual input of character info to increase the ease-of-use of the keeper and decrease the length of character creation. Feel free to follow along with your keeper, or start with the basic one I’m working on and go from there!
I’m going to go top-to-bottom while covering these features. The first function I want to cover is data validation. Basically, for our purposes, this allows us to create a drop-down list for folks to use to pick from a list. For example, in our Offworlders keepers, we can list the Classes we want to have available. In my sheet, I have left an open box next to my Class heading. If you right-click a cell, at the very bottom of the list you’ll find the data validation option. Go ahead and click that, and a SCARY INTIMIDATING WINDOW will pop up. Don’t worry! I’ll break it down for you:
So, here’s an opportunity to make a choice. The easiest thing to do here is to just choose a List from items and type out each Class. But I’m going to go a step further and List from a range. I’m going to create a table in another part of the sheet to list this data. In order to keep this data separate, I’m going to create a second “Data” sheet. At the very bottom of your window there’s a list of sheets, on the left side is a plus icon to create new sheets:
So, this Data sheet will be a bit of a playground for us. We can put information in here as organized or disorganized as we wish, and later we can right-click on the tab and Hide it from the public eye. Later, we can click on the View tab at the top of the sheet and recover it from the Hidden sheets. So why have I decided to use a range of cells as a list instead of just listing the Classes as items? One reason is, if I ever want to add more Classes for players to choose from, I will just need to add them next to my range in the Data sheet.
Speaking of ranges, there’s one more shortcut we can use here. First I will highlight my cells with the Class names in the Data sheet. Then, I will right-click, and choose Define named range. I’ll call this range “classes”.
Speaking of ranges, there’s one more shortcut we can use here. First I will highlight my cells with the Class names in the Data sheet. Then, I will right-click, and choose Define named range. I’ll call this range “classes”.
Now when I create the drop-down list for Classes, in the Criteria box, I can just type “classes” instead of the much more cumbersome and forgettable “Data!A2:A5”. Additionally, when I add those extra Classes to my Data sheet, I can just edit the “classes” named range to include them without having to change every related data validation function. Nifty!
Now I want you to imagine a truly terrifying scenario...a player shows up to your game of Offworlders and *GASP* they haven’t read the rules yet! Okay, maybe not so scary for a game as easy to pick-up-and-play as Offworlders, but nonetheless...the first mechanics-related choice they have to make is revealed as a list for them. They don’t need to go read through the rules to know that these are the Classes they can choose from.
Now I want you to imagine a truly terrifying scenario...a player shows up to your game of Offworlders and *GASP* they haven’t read the rules yet! Okay, maybe not so scary for a game as easy to pick-up-and-play as Offworlders, but nonetheless...the first mechanics-related choice they have to make is revealed as a list for them. They don’t need to go read through the rules to know that these are the Classes they can choose from.
Our next heading to consider is Look. Offworlders doesn’t offer much in terms of suggestions here. To be fair, characters in science-fiction have some of the most potential to appear radically different from one another, even within the same Class. What Offworlders hopes to achieve in this description is, “Anything that will help everyone start getting a picture of your character in their imagination.” What easier way to do this in an online character keeper than with a digital image?
When @greininghaus polled some of our Gauntlet Slackers about the best features of online keepers, one of the most commented-on was the IMAGE function. You see, there are two ways to get an image onto a sheet: clicking Insert>Image from the taskbar or using the IMAGE function. What’s great about the function is that it secures the image within the cell boundaries and maintains a predetermined size. Let’s leverage that capability to change up our Look heading.
First let’s find an image on the internet we’d like to use and copy its URL. Let’s use this piece by Olivia Gulin for example: https://images2.imgbox.com/33/df/iCEREKaC_o.png
Next, clicking on the Look cell will show in the formula bar (that’s the one with the “fx” symbol next to it) just the text we have put in: “Look”. We’re going to delete that and replace it with this:
=IMAGE("https://images2.imgbox.com/33/df/iCEREKaC_o.png")
Yay! A...tiny picture. That’s because by default the image will be resized to fit in the cell while maintaining aspect ratio. Let’s make this row a little bigger so that we can see our image clearer. For now I will resize it to five rows’ height.
Well, that works just fine for us, the illuminated folks who now know how the IMAGE function works. But we don’t want our future players to have to read all of these articles just to use the sheet. How can we make this easier for them? I’ll add a second row beneath the image cell and indicate that the URL should be entered there. Then I’ll edit the function to say:
=IMAGE(B6)
When @greininghaus polled some of our Gauntlet Slackers about the best features of online keepers, one of the most commented-on was the IMAGE function. You see, there are two ways to get an image onto a sheet: clicking Insert>Image from the taskbar or using the IMAGE function. What’s great about the function is that it secures the image within the cell boundaries and maintains a predetermined size. Let’s leverage that capability to change up our Look heading.
First let’s find an image on the internet we’d like to use and copy its URL. Let’s use this piece by Olivia Gulin for example: https://images2.imgbox.com/33/df/iCEREKaC_o.png
Next, clicking on the Look cell will show in the formula bar (that’s the one with the “fx” symbol next to it) just the text we have put in: “Look”. We’re going to delete that and replace it with this:
=IMAGE("https://images2.imgbox.com/33/df/iCEREKaC_o.png")
Yay! A...tiny picture. That’s because by default the image will be resized to fit in the cell while maintaining aspect ratio. Let’s make this row a little bigger so that we can see our image clearer. For now I will resize it to five rows’ height.
Well, that works just fine for us, the illuminated folks who now know how the IMAGE function works. But we don’t want our future players to have to read all of these articles just to use the sheet. How can we make this easier for them? I’ll add a second row beneath the image cell and indicate that the URL should be entered there. Then I’ll edit the function to say:
=IMAGE(B6)
I’ve also centered the image and text, made the URL font smaller, and shrunk the row containing it to make it less intrusive, but that’s completely optional (and similar suggestions will be made in a future post). Sometimes when I’m prepping to run a game I’ll create a Pinterest board to share with my players. They may find some potential character portraits there! If anyone has an image they want to upload, point them to a service like imgur or imgbox.
Next let’s look at the Attributes. You’ll see here that I have chosen to abbreviate these to make them fit together more easily. In doing so, I have sacrificed some clarity, but we can alleviate that by adding a note. Right click on one of these abbreviations and choose Insert note. You can add as much information as you’d like in this note, but I’m content with just defining “STR” as “Strength”. Hovering over a cell with a note, as indicated by the marker in the corner, will show the note in a window.
Next let’s look at the Attributes. You’ll see here that I have chosen to abbreviate these to make them fit together more easily. In doing so, I have sacrificed some clarity, but we can alleviate that by adding a note. Right click on one of these abbreviations and choose Insert note. You can add as much information as you’d like in this note, but I’m content with just defining “STR” as “Strength”. Hovering over a cell with a note, as indicated by the marker in the corner, will show the note in a window.
Now, go ahead and try to type “+1” into a cell under an attribute. You’ll see that it displays just a “1”. That’s because spreadsheets are designed to do math stuff, basically. Now, we could instruct our players to type in =”+1” but that’s a bit of a pain. This is a great opportunity to use the list of items for data validation. Here’s a little trick: we’ll use some Unicode symbols to fool the formula bar into not math-ifying our modifiers. We’ll use the full-width versions of the symbols so they look pretty close. You’ll want the list to look like this: －1,0,＋1,＋2,＋3
Moving on, we have Skills. You know what to do there. Abilities, however, may prove to be a little more complicated. There’s a couple of problems to tackle here.
Firstly, different Classes have different Abilities, so it’s not so simple to create a drop-down list for them. There is a solution, but it will require us to learn some new functions. Before we do, let’s set up a table with each of the Ability names in our Data sheet, next to our Class list. It will end up looking something like this:
Moving on, we have Skills. You know what to do there. Abilities, however, may prove to be a little more complicated. There’s a couple of problems to tackle here.
Firstly, different Classes have different Abilities, so it’s not so simple to create a drop-down list for them. There is a solution, but it will require us to learn some new functions. Before we do, let’s set up a table with each of the Ability names in our Data sheet, next to our Class list. It will end up looking something like this:
Go ahead and make B2:G5 a named range as “abilities”. Now, in Column A, underneath the classes, we’ll make rows for each character on the sheet, like so:
Now we’re ready to learn about a powerful lookup function combo: INDEX and MATCH. Now, I’ll be the first to admit that figuring these out and getting them to work properly can be a little tricky. VLOOKUP is simpler and easier and will work in many situations, but this specific circumstance is a good opportunity to demonstrate INDEX/MATCH. If you want to read more about how these two functions work in tandem, I would recommend you check out this article. In the meantime, we’ll get to work on our formula, starting in cell B7.
Okay, there’s a lot going on here! Let’s try to break it down. INDEX asks for a few things: a reference, which I like to remember as “what I want it to output from”, and then a number of offset rows or columns. To determine the number of offset rows, we’ll use MATCH, which generates a number corresponding to a search key’s position in another range, which I like to remember as “where I want the formula to look”. The 0 at the end makes sure that MATCH looks for an exact match, which we’ll need. You can see from the highlighted boxes which range corresponds to our key names here, and the search key is a reference* to the drop-down list for the first character’s Class (take note that it’s not the C4 cell in this sheet, but the C4 cell in the Characters sheet - we distinguish that by adding the name of the sheet and an exclamation point before the cell reference). So, when we change Character 1’s class to Warrior, this happens:
Cool! ...so what? Well, let’s make a data validation box for one of Character 1’s Abilities and reference that range in the criteria (making a named range like “character1” is easiest). Now, when that player chooses their character’s Class, the list of Abilities will be restricted to those available to that Class. Nifty!
Now our second problem comes into play. As a GM and a player, I can’t be bothered to just memorize exactly what the Hardy or Brute Abilities do, so a rules reference here is handy. We can use our INDEX/MATCH skills here to similar effect! You’ll want to add the description of the Abilities on the same row as their names, in the same order you’ve listed them, like so:
Now our second problem comes into play. As a GM and a player, I can’t be bothered to just memorize exactly what the Hardy or Brute Abilities do, so a rules reference here is handy. We can use our INDEX/MATCH skills here to similar effect! You’ll want to add the description of the Abilities on the same row as their names, in the same order you’ve listed them, like so:
Oh boy, that wasn’t time consuming at all, was it? We can only hope that our efforts will save people time in the long run, perhaps by sharing our work with the gaming community or typing up multiple long-winded articles on the subject. Now that we have our table set up, make sure to give your range a name (I’ll call mine “descriptions”). Insert a new row under your Ability drop-down, and get ready for another formula:
Oh right, that’s what Brute does! So, this formula should look mostly familiar. Let’s go over the differences. First, the output we’re pulling from is different: the “descriptions” range. The row offset stays the same*, but we also want to pull out a single cell’s contents instead of just pouring all of them out. Hence, we add an additional MATCH function, searching for the ability name in our list of those available to “character1”. Think of it like the classic Battleship game, where the first MATCH calls out which row we want based on the class and the second MATCH calls out which column we want based on the ability name.
Okay, now you may be thinking, “Geez Mike, that’s a lot of work to save not a lot of time!” Unless you’re a programmer, in which case you’re probably really jazzed right about now. But you may still have a point. In Gerrit’s slack thread, @GauntletRPG pointed out the downside with these intricate and advanced keepers with all of their automated bits and bobs: they’re prone to falling apart under even light stress. For example, if you want to use an alternate class or playbook, or copy over cells to other parts of the sheet.
Additionally, if there are Abilities that require you to make a choice within them or aren’t otherwise symmetrical (good luck following these steps for Masks’ playbooks, for example), trying to edit those into the cell’s text will require you to copy the cell’s contents, paste them back into the same cell as “values only”, and edit from there. That’s a little bit beyond the scope of technological aptitude that I expect from players using my keepers. If you find yourself in that situation, I may suggest that you just leave your abilities and descriptions in a table format on a separate sheet in your keeper. Players can then just copy the contents of those cells into their character sheets. If necessary, you may need to ask them to paste the text into the formula bar to maintain your formatting.
Now, you’ll notice that those INDEX/MATCH formulas that don’t have Abilities chosen to reference yet return an unsightly #N/A error message. If you want to hide that, you can wrap your formula with an IFERROR function, like this:
=IFERROR(INDEX(descriptions,MATCH(C4,classes,0),MATCH(B16,character1,0)),"")
If your eyes haven’t fully glazed over just yet, there’s one more feature I would like to share with you that’s relatively new to Google Sheets: checkboxes! To insert a checkbox into a cell, highlight it and click Insert>Checkbox from the taskbar. Check out how I’ve used these for our Advancement heading:
Okay, now you may be thinking, “Geez Mike, that’s a lot of work to save not a lot of time!” Unless you’re a programmer, in which case you’re probably really jazzed right about now. But you may still have a point. In Gerrit’s slack thread, @GauntletRPG pointed out the downside with these intricate and advanced keepers with all of their automated bits and bobs: they’re prone to falling apart under even light stress. For example, if you want to use an alternate class or playbook, or copy over cells to other parts of the sheet.
Additionally, if there are Abilities that require you to make a choice within them or aren’t otherwise symmetrical (good luck following these steps for Masks’ playbooks, for example), trying to edit those into the cell’s text will require you to copy the cell’s contents, paste them back into the same cell as “values only”, and edit from there. That’s a little bit beyond the scope of technological aptitude that I expect from players using my keepers. If you find yourself in that situation, I may suggest that you just leave your abilities and descriptions in a table format on a separate sheet in your keeper. Players can then just copy the contents of those cells into their character sheets. If necessary, you may need to ask them to paste the text into the formula bar to maintain your formatting.
Now, you’ll notice that those INDEX/MATCH formulas that don’t have Abilities chosen to reference yet return an unsightly #N/A error message. If you want to hide that, you can wrap your formula with an IFERROR function, like this:
=IFERROR(INDEX(descriptions,MATCH(C4,classes,0),MATCH(B16,character1,0)),"")
If your eyes haven’t fully glazed over just yet, there’s one more feature I would like to share with you that’s relatively new to Google Sheets: checkboxes! To insert a checkbox into a cell, highlight it and click Insert>Checkbox from the taskbar. Check out how I’ve used these for our Advancement heading:
You can see here what it looks like when a checkbox is marked, and I’ve also left notes for the XP costs of each Advancement. Checkboxes can also be leveraged for conditional formatting, something I will cover in the next article on polishing your online character keepers.
*As a footnote, I want to mention how handy locking cell references can be when it’s time to copy and paste your cells for new characters’ sheets, etc. Especially since I forgot to do so in the screenshots for these formulas. Let’s say I’m copying over the INDEX/MATCH formula for my first Ability slot down to the next one. We move down two rows, so Sheets automatically adjusts the reference down two rows as well: C4 becomes C6. Handy, except for when it isn’t. So we can lock the reference to that cell so that when it gets copied over it doesn’t move. You lock a cell reference by putting a $ symbol in front of it. So, if we change the reference to $C$4, when we copy it over it will still reference C4. But, what about when we want to copy over this whole sheet to the next column for our other characters? We don’t want to be referencing C4, but rather H4, and so on. In that case, we will change the reference to C$4 - the 4 stays the same, but the column automatically adjusts. Also, all of our named ranges will stay the same. Again, this is usually a good thing, but you may need to change some of those formulas to reflect those changes (references to “character1” come to mind).
*As a footnote, I want to mention how handy locking cell references can be when it’s time to copy and paste your cells for new characters’ sheets, etc. Especially since I forgot to do so in the screenshots for these formulas. Let’s say I’m copying over the INDEX/MATCH formula for my first Ability slot down to the next one. We move down two rows, so Sheets automatically adjusts the reference down two rows as well: C4 becomes C6. Handy, except for when it isn’t. So we can lock the reference to that cell so that when it gets copied over it doesn’t move. You lock a cell reference by putting a $ symbol in front of it. So, if we change the reference to $C$4, when we copy it over it will still reference C4. But, what about when we want to copy over this whole sheet to the next column for our other characters? We don’t want to be referencing C4, but rather H4, and so on. In that case, we will change the reference to C$4 - the 4 stays the same, but the column automatically adjusts. Also, all of our named ranges will stay the same. Again, this is usually a good thing, but you may need to change some of those formulas to reflect those changes (references to “character1” come to mind).
0 Comments
RSS Feed
