Background

As I sit here questioning my life choices, checking off another hundred missable items, save points, and bestiary entries along the route to platinuming Tales of Vesperia, I wondered if all these repetitious spreadsheets I use to gray out items I’ve found might be of use to the greater gaming community.

So I cleaned it up and made it generic and am presenting it for use for others. Some features include:

• Custom colors when checking off items from a list, so it automatically styles the rows according to your needs, so you can check off “done” things with one color and “missable” things with another
• Colorizing based on columns being greater or less than each other, so you can have a number of PoopyMcGubbins that you need to collect, and a number that you have currently, and have it color that row one way when you still need some, and differently when you’ve collected enough
• No-keyboard updating. That is, once you’ve made your lists, you can use mouse or trackpad only to mark off the colors or to increment or decrement the PoopyMcGubbin counters, rather than having to move the mouse, click into a cell, move your hand to the keyboard to find the right key or number, and so on

Download

To use it, first download LibreOffice, as I made it for this open source spreadsheet tool and not Excel:

https://www.libreoffice.org/download/download-libreoffice/

Then download the spreadsheet:

GameUtilitySpreadsheet.ods

Then get to using it. There’s an instructions page if you want it, but I think it’s pretty self-explanatory. Duplicate the Data sheet if you want more separate sheets (like one for items, one for quests, etc.)

If you choose to not enable macros, then the only thing that doesn’t work is the double-click to increment and decrement the gubbin counters, you can still use the functionality, you just need to edit the numbers the manual way. You can leave on the spreadsheet protection, it’s mostly there just so you don’t break some of the key cells that are referenced by all the macro rules; you can still edit all the columns you want in the data sheets.

FAQ

How do I start?

Just open the spreadsheet and add data. It’s a normal spreadsheet, change the columns, duplicate the Data sheet if you want more separate sheets.

What are these Style Definitions in the second tab?

Don’t delete or rename that tab please. You change the values next to the colors so that you can use those letters to mark other items later. So if you want “m” to mean “missable” and be bright red, and “d” to mean “done” and mean you already did that thing, then empty out all the ones next to the colors except the ones you care about.

How about the X>Y things, those look complicated?

Nope that’s just the PoopyMcGubbin counter. If you don’t intend to keep track of how many of this and that you have or need, then you can ignore it. But if you do, then the point is to use one of the columns in the Data sheet (X or Y) as the number of each item you have, and the other as the number you need. Or something like that, it’s custom and up to you. The way the > and < work next to that is how you want to color them. So if you want to mark rows where X>=Y as red and X<Y as orange, then use this:

The things in the “VS” column to the right are just a reminder of your options, please don’t edit those.

What are those ▼ ▲ things?

That’s for keyboard-free incrementing and decrementing of the gubbin counters. It’s annoying to have to click into a field to increase the number by one, it’s a lot of moving the cursor around, finding the right key on the keyboard, and doing basic +1 math. Insert Ain’t Nobody Got Time Fo Dat meme here.

Double-click the up-arrow to the right of X and it’ll increment, double-click the down arrow and it’ll decrement. Incrementing from blank goes to 0 and then 1. Decrementing from 1 goes to 0 and then blank. The reason for the difference is that if either X or Y is blank, it won’t colorize that row.

I hate those gubbin counter columnns, they clutter up the page.

Yep, just hide the columns if you don’t need them. All the coloring rules work for B2:Z9999 so you can spare hiding a couple ugly columns.

How do I make new tabs?

Just right click the “Data” tab and select “Duplicate”.

There’s a caveat to this though. I won’t have the increment and decrement macros working. Those don’t copy along with duplication. I tried but I can’t make it stick. If you want it working on new tabs, go to Sheet -> Sheet Events while on that new coped tab…

Then select Double Click, and find the onCellDoubleClicked and select OK.

Incidentally, in order to delete a sheet/tab, go to Tools -> Protect Sheet to remove protection first, then you can delete it. There’s no password for the protection, it’s just there to make messing up the macros a little harder.

This is awesome but why did you spend time on this?

Cuz I’m a nutjob that loves coding, for work or for play. I have other stuff up like the Atelier Recipe Finder.

I like you, you’re crazypants.

Thank you.

Any parting advice?

• Don’t eat yellow snow
• Never pet a burning dog
• Tell all your friends to buy StarCraft 2

Addendum

How does the double-click thingy actually work?

To do something similar, make macro code in OpenOffice BASIC code. Go to the Sheet -> Sheet Events… menu to bind it to the double-click event. Here’s the horribly unsearchable API spec that wishes it was javadoc which would have the basic kindness of showing the values and methods that are inherited by a class or interface, instead of needing to check every single dependent tree item for the thing you need.

For those interested, here’s the gawdawful code for the double-click mechanism:

Sub decrement( oSheet, iCol, iRow )
	Dim oCell as Object
	oCell = oSheet.getCellByPosition(iCol, iRow)
	With com.sun.star.table.CellContentType
		Select Case oCell.Type
			Case .EMPTY
				' Do nothing
			Case .VALUE
				If oCell.Value >= 1 Then
					oCell.setValue( oCell.Value-1 )
				Else
					oCell.setValue( 0 )
					oCell.setString( "" )
				EndIf
			Case .TEXT
				' Do nothing
			Case .FORMULA
				' Do nothing
		End Select
	End With
End Sub

Sub increment( oSheet, iCol, iRow )
	Dim oCell as Object
	oCell = oSheet.getCellByPosition(iCol, iRow)
	With com.sun.star.table.CellContentType
		Select Case oCell.Type
			Case .EMPTY
				oCell.setValue( 0 )
			Case .VALUE
				If oCell.Value >= 0 Then
					oCell.setValue( oCell.Value+1 )
				Else
					oCell.setValue( 0 )
					oCell.setString( "" )
				EndIf
			Case .TEXT
				' Do nothing
			Case .FORMULA
				' Do nothing
		End Select
	End With
End Sub

Sub onCellDoubleClicked(pEvent)
	Dim oSheet as Object
	Dim oCell as Object
	Dim iRow as Integer
	Dim iCol as Integer
	oSheet = ThisComponent.CurrentController.getActiveSheet()
	iCol = pEvent.CellAddress.Column
	iRow = pEvent.CellAddress.Row
	If iCol = 3 then 
		decrement( oSheet, iCol-1, iRow )
	EndIf
	If iCol = 4 then 
		increment( oSheet, iCol-2, iRow )
	EndIf
	If iCol = 6 then 
		decrement( oSheet, iCol-1, iRow )
	EndIf
	If iCol = 7 then 
		increment( oSheet, iCol-2, iRow )
	EndIf
End Sub