I’m stepping on Alice Keeler’s corner and I think I need to watch my back!

I have had a number of spreadsheet posts (More VLOOKUP Magic: A Half-Baked Random Generation G.Sheet Trick,  The Magic of VLOOKUP: G.Sheets, Boss Fights, and Badges, More G.Sheets Magic: Nested IFs, and Random Numbers) and her is one more.

In my Dreadsheets, whether they are clashes or boss fights, I try to include “Battle Damage” or images that show when a student or team’s hit points decrease. This provides a bit of novelty and is more interesting than the just watching points trickle down. If a team is on the losing side it also provides for a “Fun Fail State” possibility. I was recently asked how I create this battle damage. The answer is both complicated and yet fairly straight forward.  The VLookup function is the foundation of the Battle Damage Mechanic.

To create Battle Damage the first step is to create a Spreadsheet with two tabs. In the picture below I have 4 but you only need two. The first is the Home Tab (mine is labelled PROJECTION) where the battle damage is displayed. The second tab is where you will create the table where the battle damage will come from (I labeled mine Damage).

Next, create a Three column table on the Damage page. (I actually have 2 tables in the image below. One for the Sentinels and one for the Hunters but only one is needed.) Here are the three columns. A = The Hit Points that will be referenced. B = The Graphic that will be referenced. C = the url link to the image from the google drive.

Screenshot (57)

So, this is where it gets a bit complex. The URL Link comes from a published Google Drawing. Create a Google Draw and when it’s finished click on File and then Publish to the Web.

Screenshot (59).png

Then copy the link.

Screenshot (58)

And Paste it in Column C.

Keep creating Google Draw files and adding them to Column C. One tip is to start with a base picture and find Battle Damage Transparencies (bullet holes, fire, smoke, etc…) and add some to the original picture. Then Make a Copy and add more battle damage. Keep doing this until you are ready for the final picture and then go wild. Keep adding the published file links to Column C until you are finished.

Now, Column B is the Graphic that will be referred to on the PROJECTION display. This is a very simple formula =image(_). For Example, if I want to display the image link in Cell 2 type =image(C2).

And Column A. This the range the Hit Points will reference.

Capture

For example, in this battle the students will start with 500 Hit Points. When they lose a point they will have 499 points. The lookup function will look in column A and find the range between 450 and 500. It will then pull the image from column B and display it. In this step you might need to play a bit but it will display the image in the lower cell. For example, in the 499 HP example it will pull the image next to the 500 HP (Cell B13).

Lastly, lets turn to the Display Page. Here is mine.

Screenshot (56)

I shrink this to a half page so I can display Plickers Questions on the board next to it. Here is the function that looks up which image to display.

Capture

=if(D6=”,”, vlookup(D6,Damage!$A$2:B,2))

  1. “if” refers to the first D6 cell reference. It means “if there is a number in D6 then move to the vlookup function.
  2. The “,” is means “any number here”

Lets move to the vlookup function.

  1. The second D6 is telling the vlookup function where to find the number to look up in Column A.
  2. Damage! tells the function which tab to look at to find the table.
  3. $A$2:B is telling the function where column to find the number begins and in which column the table ends. (I don’t exactly know what the $ are for.)
  4. ,2 is telling the function which column to look into in order to find the image. In this case the second column of the table.

So there it is. Use the Google Drive, the Google Draw, and Google Spreadsheets to create your very own Battle Damage in your very own Dreadsheets! Play and Share!