Expert Spreadsheet Techniques, Tips from Character Spreadsheet Veterans Options

[DamienKnight]

I have gotten a few questions about the gears behind the SR4 Character Sheet I have worked on, and thought I would share the answers with the community. I intend to discuss all of the advanced features in the SR4CG_DK sheet, including Character Sensitive Selectable Lists, making your sheets printable and the Matrix functions that drive the Armor Outfits.

But first, some basic questions I hear alot.

1. Why a spreadsheet? Why not Java, Python, C++ ..etc?

Spreadsheets work on a wide variety of systems.
Spreadsheets support most functionality needed in a character generator/manager.
Spreadsheets are easy for any user to edit, making them more friendly to community development.

2. Why so many formulas... why not use Macros?

Visual Basic is grrrreat! I use it often at work and love it. Unfortunately macros written in excel 2003 may or may not work in excel 2007 or excel 2010, and never work in Open Office. Early versions of the SR4 sheet used macros for various purposes, but too many users were hampered by the incompatibility of embedded spreadsheet macros.

I still keep a few macros in the sheet, but none that basic users will require to use the sheet to its fullest. Mostly there are just dice roll macros to simulate dice rolls in a pinch, and developer tools. My favorite is the 'Developer_Mode' macro, which can automatically switch a sheet between development and production modes. Ill explain that more in a later post.

3. Why such a basic design? Earlier versions had sweet borders, more colors etc.

All versions of Excel before excel 2007 have a built in style limit. Because of the weird way Excel duplicates the same style into multiple style instances, and has a built in limit of around 2000 styles, large spreadsheets that use a variety of borders have problems. The SR4CG hit that limit around BETA 8. I wrote a macro to remove ALL borders/Highlighting from the sheet, and redesigned the sheet to have a more basic style.

Color coding is important! To make a sheet easier to use, its helpful to fill cells with colors that indicate the cells function. Cells that contain drop down lists should be one color, while cells that require a user to manually enter data are another color.
Its good to customize your palette and create two different shades of your primary colors so that you can alternate shading in long lists... it can make it easier to follow a row across a long page.

That does it for the basic questions. Next post will be about Data Pages and Names.