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.

[DamienKnight]

Data Pages and Named Ranges

The guts behind a spreadsheet that differentiate it from a simple Printable PDF are Tables. These tables allow character options to be selected from lists, and validated for rules accuracy. Can my street samurai start with Move by Wire 2? What Echoes can my Technomancer choose from?

These tables are not important for the user to see, so they can be put into hidden Data pages. Create a new Tab, put a few tables in it, give it a name that ends in '_Data' and you are ready to drive the character creation experience. Users wont have to have every rule or option memorized... it will all be built into the sheet.

Once the data tables are setup on data pages, you are going to want to start using Named Ranges. A Named Range is basically a Name that points the sheet to a specific tab and cell range. For example instead of referring the the character's name as 'Main_Sheet!C4', you could instead select cell C4 on the main sheet and add a name 'CharName' that refers to that location.
(In Excel there is a name box in the upper left where you can enter a name for the currently selected Cell or Range of Cells. In Excel 2003 you can select 'Insert->Names' and then add/remove/edit names from a complete list of all named ranges in the sheet.) Now any time you need to refer to the character's name, you can simply put 'CharName' in your formula and the spreadsheet will know exactly what cell you are referring to.

After naming specific cells, you need to start naming your Lists. A list is a single column that contains a list of values. For example, if you wanted the user to be able to select a magic type for his character. You dont want them entering in any random value... they may end up typing 'Magician' instead of 'Mage', or 'Physical Adept' instead of 'Adept'. This can make it difficult for your formulas to determine weither they should have access to Adept powers, Spells, or nothing.

So rather than having the user enter in a value, you let them select from a list. Go to your data page and make a list of every magic type they can choose from. You want to select a default like 'None', or include a blank space in your list.

None
Adept
Magician
Technomancer

Now select your list and Name the list something like 'MagicTypeList'. Now go to the Main Sheet cell where the user is specifying their magic type and edit the Validation of the cell. (Excel 2003 'Data->Validation). Select 'List' validation, then for the 'Source' enter '=MagicTypeList' (no quotes).

Now you should see a drop down where you can pick from your list of Magic Types.

And now we are ready for full tables. A table is like a list but it extends to 2 or more columns. If we want to lookup a value in the table we use the vlookup function. Get a complete tutorial on vlookup here: http://www.timeatlas.com/5_minute_tips/gen...lookup_in_excel

For example, lets say we have created an adept and we want to select his powers. We have created a list of every power selectable, now we want to total up the power cost.

On our data page, we add a column next to our list of powers and put in it the Power Point (or Magic) cost per level of the power.


Freefall .25
Motion Sense .5
Killing Hands .5

Now select both columns and name the range 'AdeptPowersTable'.

Now on the character sheet where the user has select an Adept power from the list of Adept Powers, add a column with a formula that does a lookup of the Adept Power cost.

CODE

   A               B
1  Adept Powers  Cost
2  FreeFall     =vlookup(A2,AdeptPowersTable,2,FALSE)

Vloookup looks for the value in A1 (FreeFall) in the AdeptPowersTable's left most column, then returns the value from the second column. The 'FALSE' tells excel to only return an exact match. Without FALSE excel will try and find the first closest guess, which is TERRIBLE for most character sheet formulas.

It should resolve to this:

FreeFall .25

Now what if the user wants to select multiple levels of FreeFall? Is that even allowed? The sheet sure doesnt know the answer yet!

Lets add another column to the AdeptPowersTable that specifies a limit. Most powers are limited by the Characters Magic Rating, others have an inherent limit. For those limited by the characters magic rating, we can enter a forumla in the data table that returns the users magic rating, or we could just use 999 to specify no limit, then code the Magic Rating limit into the Adept Powers Sheet where we are selecting powers.

FreeFall .25 999
Motion Sense .5 1
Killing Hands .5 1

Now on the Adept Powers Table, lets pull in the limit. The user does not need to see the limit, so we will put it off the page to the right, then put in a cell that lets the user specify the limit:

CODE

    A               B                                      C                          G
1  Adept Powers   Cost                                     Rating
2  FreeFall      =vlookup(A2,AdeptPowersTable,2,FALSE)*C2                 =vlookup(A2,AdeptPowersTable,3,FALSE)

Now we need to add validation to column C to force users to select a valid rating. Lets say that character's magic rating is in a cell named 'modMagic' (for modified magic rating).

Lets change G2 to:

=MIN(vlookup(A2,AdeptPowersTable,3,FALSE),modMagic)

The MIN statement will select the lower of the two values... either the limit defined in the AdeptPowersTable, or the characters magic rating.

Now for C2 go to Validation and choose
'Whole Number'
'between'
Minimum: '0'
Maximum: 'G2'

Now they can specify the rating, but if it exceeds the allowable limits it will warn them.

[DamienKnight]

Character Sensitive Selectable Lists

Sometimes a character should not be able to select an option from your list.

For example, if you do not have any SURGE qualities, you should not be able to
select Meta genetic qualities. Or if you are not Magically active, you should not
be able to select the 'Astral Chameleon' quality.

This is best explained by looking at the SR4CG_DK sheet.
If you dont have a copy, check my sig for a link.

Open the sheet and unhide the Qualities_Data sheet. (in excel 2003 select 'Format->Sheet->Unhide')

The root of the 'Selectable Qualities' table is a list of all selectable qualities (column E).

The table has three columns of lookup data, BP cost (F), Book (G) and Page (H).

The next Five columns are flags used to define when the quality is selectable.

For example, see 'Adrenaline Surge' at the top. The Metagenetic column (I) is a 'N',
as is the Infected Column (J). These indicate that the Quality is not
in the Metagenetic or Infected group of qualities.

Column K is 'Y' indicating the quality is Normal. This means it can be selected as a default.

The next two columns specify if the quality is selectable by AI and Free Spirit Characters.

Next, column (n) is a flag indicating P for 'P'ositive quality or 'N' for 'N'egative quality.

The last column is a general 'Disable' column. For most qualities it is blank. For qualities
with special requirements, it contains a test that results in FALSE if the character
does not match the requirements.

See 'Astral Chameleon' (row 22). Cell O22 has the simple test: =NOT(Is_Magic)

This could say, =IF(Is_Magic,FALSE,TRUE) or =IF(Is_Magic=TRUE, TRUE, FALSE).
But a NOT statement returns a TRUE/FALSE, so it was simpler.

Now look at column D. This column examines all of the other columns and determines the Selectable state of the quality.

CODE

=IF(E17="",FALSE,IF(O17,FALSE,IF(AND(K17="Y",
NOT(Is_AI),NOT(Is_FreeSpirit)),TRUE,IF(AND(I17="Y",
OR(Is_Changeling,AND(Metavarients_Surge_House_Rule,
Is_Metavarient))),TRUE,IF(AND(J17="Y",Is_Infected),
TRUE,IF(AND(L17="Y",Is_AI),TRUE,IF(AND(M17="Y",
Is_FreeSpirit),TRUE,FALSE)))))))

First, if E17 = "" (two double quotes indicates a NULL or Nothing) then D will be FALSE.

Otherwise, it will test if O17 is TRUE. If O17 (the Disable Column) is TRUE, then Selectable will be 'FALSE'.

Next it goes through and checks each column in the table to see if the character meets the requirements. The final result will be 'TRUE' if the quality is selectable by the character, and FALSE if it is not.

Now we have a TRUE and FALSE column, but we still have a full list of every quality. We could move the 'Selectable' column to the right end of the table, then simply let the user pick any quality they want, and then do a VLOOKUP on the quality to return the TRUE/FALSE selectable status of what they chose, then if they chose something they shouldnt we could use Conditional Formatting to Highlight the column red.

We do want to Highlight errors, but even better we want to prevent the user from even seeing the INVALID options in their drop down list.

Another option is to edit the Qualities Column (E) so that each entry basically said: =IF(D22=TRUE,"Astral Chameleon","")

This solution would prevent invalid options from appearing in the dropdown, but in Excel2003 this left us with actual BLANK spaces in the drop down list. UUUUUGGGGLY!

The workaround I discovered was to create another List, which was a list of actually Selectable Qualities, then make that list a lookup against the original list.

But to get the list to lose the spaces I had to only select options where the Quality was selectable.

So I added Column B and C. Column C is for a full list of all Selectable Qualities, and Column B is for a list of only selectable Negative Qualities. The formula in C basically numbers the rows when they are selectable, and leaves a 0 if they are not.

See Cell C3: =IF($D18,MAX(C$16:C17,0)+1,0)

If D18 is true, then the quality is selectable. If it is selectable, we want to increment the count, otherwise put a 0.

To increment the count, we look at the MAXimum value from the numbers in the list above. C16 has a 1 and C17 has a 2, so the max between C16 and C17 is 2. We add 1 to that and the result is 3.

You can see where this really is important when you get to row 23: Astral Chameleon is not selectable (assuming you have not added 'Adept' or 'Magician' etc. to the Main_Sheet's 'Special' selection), so the number in column C is 0.

The next line 'Astral Sight' picks up at 7, where column 21 left off.

Now lets look at the selectable qualities list. See Column Q and R.

Column Q is a simple number list, from 1 to the maximum qualities we could possibly see in the list.

Column R has a formula that performs a lookup against the numbered portion of the selectable qualities table.

R3: =IF(Q3<=C$15,IF(VLOOKUP(Q3,Qualities_Index_Table,3,FALSE)=0,"",VLOOKUP(Q3,Qualities_Index_Table,3,FALSE)),"")

First, it checks to make sure that Q3 does not exceed the count of how many qualities are currently selectable (see C15, where it gets the maximum from column C).

If Q3 (1) does not exceed the maximum (should be around 284), then it will perform a lookup.

Now if you do a lookup on a table and there are no results found, you may end up with a 0 in the column. To prevent the character sheet from having any random 0's in it, most of my lookups are actually a double lookup.

First it performs the lookup and checks for an empty or 0 value. If the result is empty, then I assign "" (Null).

If there is actually a result to see, then I do the same lookup again to populate the value in the cell.

So the lookup checks Q3 (1) against the qualities Index Table (Index version of Qualities table starts with the Index number in column C, instead of at the qualities list in Column E).

If should find the '1' in cell C16 and return the value from E16, which is '<< Positive Qualities >>'. That is actually a header that helps segregate the list of qualities.

Looking at Q4 we see that it found index 2, which resolves to 'Adrenaline Surge'.

Looking down Column R you should see a nice final list of selectable qualities, without uneeded spaces in it.

If you are using a program other than excel 2003, the program may automatically filter out blanks, but you should use this method so that your sheet is easily usable by all spreadsheet programs.

And that is how to make a selectable list. For another example, see selectable Metamagics. 'Magic_Data tab, columns AL through AS'.

[DamienKnight]

Printable Sheets

When I first introduced my revisions to SR4CG the most common comment I received was 'Yay, it has Printable sheets like Wizdoc's sheet!'.

I love managing my character in a spreadsheet, but when I am at the gaming table I need an easy to read summary of my character (And my GM needs a copy too), so printable is a MUST!

So through examining Wizdoc's sheet (remember, I was a programmer who knew very little about excel when I started), and through working on the printable sheets in SR4CG, I have come away with these concepts:

1. Separation: Printable Sheets must be separate from the functional parts of your sheet that track data and allow user interaction.
2. Formatting: Sheets need Consistent Fonts and Shading techniques
3. Lists: Lists are best condensed to a single Cell.


Separation

Some of the tabs in a character sheet are going to lend themselves to direct printing (such as the Pets page), but most require a clean condensed summary in order to fit them onto a page. With all of the condensing and formatting you are going to do to a printable sheet, you are going to have difficulty creating the tables of data to the right in hidden cells that complicated pages require.

Things will simply be easier if you keep printable pages separate from your pages that contain actual data.

Formatting

Use the same font for similar parts of the sheet. When displaying numbers in a cell, consider the likely number of digits, and use Narrow Fonts when necessary, to make sure larger numbers will not display a ####.

If you are using Excel 2003 and lower and you are nearing your limit for Styles, try replacing borders with thin shaded cells. A border will always affect the formatting style on at least two cells, where a shaded cell will affect only one.

Lists

When displaying a list such as the 'Skill' list in the SR4CG, you want the results to be flexible. If you create a list of cells in the printable sheet and have each cell refer to another cell where a single line of data is stored, you have a hard # of items in the list, which may not translate well.

For example, a mage may only have 5 active skills, while a street samurai may have dozens. One sheet will not be a perfect fit for both.

If you instead create a cell which combines the values into a list, then the skills area can be highly customizeable.

Step 1 is to create a cell on your Misc_Data page which contains '=char(10)'. This is the carriage return character. Name the cell 'br' or something else short and simple. (br is short for 'break', and is a common abbreviate for endline characters in Markup Languages)


Next, concatenate (to add two strings together) all data elements into one cell per line. In this example, we will combine Skill names with their ratings and an Endline character.

[code]
A B D G
1 Unarmed 2 = A1 & " " & B1 & br = D1&D2&D3&D4
2 Athletics 3 = A2 & " " & B2 & br
3 Sorcery 2 = A3 & " " & B3 & br
4

See cell G1-- I have concatenated all the cells from D into a single cell. The resulting text will look something like this:

Unarmed 2[] Athletics 3[]Sorcery 2[] (where [] is actually a little box excel displays when it cannot represent a character).

The little box you see is where the endline (char(10)) is. Currently the cell is displaying all of the list on a single line.

To make our endlines actually display correctly we need to edit the formatting of the cell. Format->Cell->Alignment->Wrap text

Once the cell has text wrapping enabled, it will display the endlines as actual carriage returns. Now make the cell larger until you can see the complete list.

That is the basic idea behind the lists in a character sheet.

Notes:

- You have the option of using the CONCATENATE statement instead of ampersands (&)
This would look like '=CONCATENTATE(D1,D2,D3)'. This is a speedy way to concatenate a large list without typing every cell name. Simply type in the '=CONCATENATE(' then click the first cell you want to include (D1), then hold CTRL and select each individual cell in your list.

CONCATENTATE only supports 30 values, and a cell can only display a maximum of something like 1200 characters.

- If you create your list in a data cell and give a name like 'FormattedSkillsList' you can have multiple printable character sheet pages that all reuse the same cell.

- This list can be searched for values easily with the 'MATCH' command. Useful when check to see if a character has a specific skill.

[DamienKnight]

Categorical Selection

When selecting Items, Wares etc. sometimes the list can get VERY long. If it takes a long time to search through a dropdown, your drop down stops being useful.

What would be useful would be to Group your large list into categories, then have the user select a category from one drop down, then have the next dropdown only display items that fall into that category.

In the SR4CG I first tried to resolve this problem with Gear. I used a numbering list similar to the Character Sensitive Selectable List method, which required a numerical mapping of Categories and items. Very hinky, and when inserting items into the final Gear list, I found myself having to disable auto calculate in excel, or my numerical mapping became corrupt.

I refined the method a bit recently when adding categories to cyberware/bioware. Finally while applying this method to a data mapping sheet at work I found a very simple solution that requires minimal work.

For our example, lets use a list of weapons.

B
1 Stun Baton
2 Baseball Bat
3 Katana
4 Knife
5 Shuriken
6 Throwing Knife
7 Desert Eagle
8 Browning Max Power

First, we need to assign a category to each:

CODE

A                B
1 Club           Stun Baton
2 Club           Baseball Bat
3 Blade          Katana
4 Blade          Knife
5 Throwing       Shuriken
6 Throwing       Throwing Knife
7 Hvy.Pistol     Desert Eagle
8 Hvy.Pistol     Browning Max Power

And we are going to need a list of Categories, for the Category Dropdown:

D
1 Club
2 Blade
3 Throwing
4 Hvy.Pistol

Note that the categories do not need to be in alphabetical order, but in the main weapon list the items do need to be listed with their group. (All clubs together, dont add a club to the end of the list, insert it after the last club).

If your downdown is on the same page as your list you can use the Address Range of the lists in your dropdown validation. Usually you want to keep your big lists hidden on a data sheet, so you will need to give the list a 'Name'. In excel 2003 (and probably many other spreadsheet programs) you cannot have validation that references a seperate sheet unless the reference is within a Named Range.

So highlight cells A1:A8 and go to the upper left naming box and name them 'WeaponListCat'

Highlight cells B1:B8 and name them 'WeaponList'
Highlight cells D1:D4 and name them 'WeaponCategories'

Now lets go to where we want to put our dropdowns. For this example lets put them on A10 and A11.

Select A10 and select 'Data->Validation', select 'List' type, then for the formula put:

=WeaponCategories

Select the dropdown in A10 and choose 'Blade'

Select A11 and select 'Data->Validation', select 'List' type, then for the formula put:

=OFFSET(WeaponList,MATCH(A10,WeaponListCat,0)-1,0,COUNTIF(WeaponListCat,A10))

Now select the dropdwon in A11 and you should only see the options 'Katana' and 'Knife'

Lets break it down:

The OFFSET works like this: OFFSET([list],[row offset],[column offset],[# of rows to include])

So our OFFSET statement is going to give us the Weaponlist, starting where our MATCH statement defines, not offseting the column at all (0), and giving us a number of items from the list equal to the COUNTIF statement.

The MATCH statement takes the category selected in A10 and searches the WeaponListCat for the first occurrence of that Category.

The list in A1:A8 has Blade in it twice, the first occurrence is the 3rd entry in the list, so match returns '3' to the formula.
Since the beginning of our range is considered a 0 offset, getting to the 3rd entry will be an offset of 2, so we need to subtract 1 from the result of the Match. Final row offset is 2.

The COUNTIF statement is used on the WeaponListCat (A1:A8) to count up how many matches there were against the selected category. Since the Blade entry appears twice in our list, the COUNTIF returns 2.

So we have OFFSET(A1:A8,2,0,2). This returns the weapon list, starting at 2 rows past the first, and going for 2 rows before stopping.

And there you have it, a categorically selected list, with a simple formula in the Validation.

If you are only looking at 2 or three categories, you could alternatively create a separate named range for each list of items, and then use a simple IF statement in the validation: =IF(A10="Group1",Group1List,Group2List) etc. This method was used on the Cyberparts page for the dropdowns in the cyberlimb modifications list. When you get more than 2 or three categories, its better to put all of the items into a single list with a CATEGORY assignment column like above.

Please post any questions or comments.

[LonePaladin]

DK, have you been fiddling around with my previous works?

I figured out the stunt for removing white-space from drop-down lists when I was working on HeroForge. It was one of those gimmicks that totally baffled the people who followed up on maintaining it.

Here's a trick for you, so that I can claim to be contributing to the topic for real:

Excel has an internal limitation on cell formatting; it's extremely large, but when your spreadsheet starts to approach the 3MB filesize, you're bound to run into it. Thankfully, there's a workaround: Abusing conditional formatting.

It's not difficult. Select the cell you want to format, and fire up the Conditional Format rule-creator. (How you go about this depends on your version of Excel.) Simply set the conditional rule so that it's always going to be fulfilled, then set the format however you want. It involves an extra couple steps, but it can keep you away from that format-limit brick wall.

[DamienKnight]

DK, have you been fiddling around with my previous works?

I figured out the stunt for removing white-space from drop-down lists when I was working on HeroForge. It was one of those gimmicks that totally baffled the people who followed up on maintaining it.

Here's a trick for you, so that I can claim to be contributing to the topic for real:

Excel has an internal limitation on cell formatting; it's extremely large, but when your spreadsheet starts to approach the 3MB filesize, you're bound to run into it. Thankfully, there's a workaround: Abusing conditional formatting.

It's not difficult. Select the cell you want to format, and fire up the Conditional Format rule-creator. (How you go about this depends on your version of Excel.) Simply set the conditional rule so that it's always going to be fulfilled, then set the format however you want. It involves an extra couple steps, but it can keep you away from that format-limit brick wall.

Thats an interesting trick. So when excel applies conditional formatting, it does not use up one of the ~2000 unique styles? Very nice. I will remember that if the SR4CG sheet ever hits that wall again. When I hit it the first time I had to create a vbscript that crunked out all of the borders and that dropped the unique styling down alot.

It also took alot of headache out too, since I was using conditional formatting to make borders shift around.

About the white-space free list, I figured that one out myself. It happened when I was working on the Metamagics list... since they are so conditional; and once you pick one, more open up; or you cannot select the same ordeal twice, etc. The blank spaces were killing me!

Originally I also used an Index in the named range formula to truncate the range based on how many values were being used, but I found some versions of Open Office were pooping out on those ranges, so I dumped them. I figure who cares how much white space is at the end of a list, so long as its not scattered throughout.

Isnt Heroforge working on a SR sheet, or am I mixing up my rumors?

[LonePaladin]

Isnt Heroforge working on a SR sheet, or am I mixing up my rumors?

Haven't seen anything hinting that the maintenance crew is doing anything like that.

I am working on one myself, though, which may be what you heard about. I'm not using Excel, though -- I'm building it in a proprietary program made for making custom chargen templates. I'm having to learn how to make everything in it from scratch, and I'm probably going to go from there to a stand-alone program later this year.

If I had to assign a "completion" gauge to my current project, I'd guess it's about 20%. Making progress, but not nearly enough to start showing off to anyone other than my wife.

[DamienKnight]

Dug through the forums and found the rumor I was talking about... seems it was HeroLab that is doing an SR sheet, not HeroForge (IMG:style_emoticons/default/smile.gif)

http://www.enworld.org/forum/media-lounge-...g-hero-lab.html

[DamienKnight]

I have added a post explaining how to create Multi-Level drop downs (Select Category, then select from a sub list based on selected Category). Its a simple trick and a staple for any character sheet using long lists. Enjoy!