Hextreme Heroscape Spreadsheet v 13.3

[IshMEL]

Hextreme Heroscape Spreadsheet v 13.3

Download it here!
Download the "Lite" version here!
Download the "HCode converter" here.


Introduction

HeroScape Hextreme Spreadsheet
Version 13.3
This spreadsheet was originally created and maintained by Matt Robertson aka Bixby, and updated by LeftOn4ya. This is based on the ideas, feedback, and various elements found on the HeroScapers website. Various other heroscapers.com users have updated this spreadsheet, and now it is maintained by IshMEL.

This post contains detailed information on the features included in the spreadsheet and how you can get the best value from using this tool. Also, post any errors, suggestions, or comments about the spreadsheet in that discussion thread. Thank you. Have fun and happy 'scaping!

Updates in Version 13.3!

In addition to adding all the new C3G units (lots!) and some other cleanup stuff, there are several major changes in this version.

First, there is now a "Lite" version. This version allows you to track your collection, see and sort all the data on units, use the Army Builder, etc. It does *not* include the ability to save armies, or the list builder. If you're not interested in either of those, use the "Lite" version -- it's faster and smaller by far.

Second, in response to feedback that the HCode identifiers were too terse to be useful, I've changed them. There's now no length limit. If you used the old system (and I suspect that's not all that many of you) and want to convert your Saved Armies over to the new system, use the HCode converter sheet here. You'll need to have macros enabled for it to work.

Third, there's a new (well, mostly new) tool for customs creators: a way to generate a posting for Heroscapers.com automagically from the custom data you entered! I've been using this and it saves a lot of time. You can generate a single posting, or use it to manage your "OP" of all your customs. Instructions are in the sheet.

As always, post your errata and desiderata here. Thanks!

Instructions
There are many connected tables and cells in this spreadsheet. To make sure you do not run into any problems, most sheets are protected so you can only enter or change information in cells that are highlighted yellow like this cell. Quite often these cells will have pull down options.

Tracking Your Collection.
You do not need to fill in the collection sheets to use tools of unit details, army creator & list builder.

Start by filling in the Set Collection sheet for full sets you have obtained. You can enter the number of sets you have bought, sold, and traded, along with the amount spent. You can also track the number of sets you want in your final collection; there is a "Standard Collection" quantity you can use as a guide.

Next, use the Individual Collection sheet to track any official individual units, terrain, or game pieces you have obtained. Again, you can track buying, selling, and trading. You can also use this to make adjustments to any partial sets you have bought (or pieces lost) -- just indicate the missing pieces were "traded away." And, you can adjust the number of units / pieces you want in your final collection. The spreadsheet will put in an initial value based on your desired sets from "Set Collection," but you can overwrite that number. (It also assumes that you only want 1 of a Unique unit.) So for instance, you want 3 RttFF sets, but only 2 Dumutef Guards. Put in 3 RttFF sets desired on "Set Collection", and then put "2" under "Your Qty Desired" for Dumutef Guard on the "Individual Collection" sheet. Or you only want 2 Roman Archers, but 4 Roman Legionnaires. Put "2" under "Sets Desired" for Roman Legion on the "Set Collection" sheet, and then enter "4" in "Your Qty Desired" for the Legionnaires on the Individual Collection page.

You can also use the Individual Collection page to generate your Trade List. Filter the "Qty Sell/ (Buy)" (Column G) using the "number filter" to show all numbers less than 0. Then go to "Trade List Wants" (Column AA) and copy that column. Paste into your favorite text editor. Repeat by filtering Column G for numbers greater than 0 and copy "Trade List Haves" (Column AB) to create your Haves List. Edit as desired.

Finally, you may use this spreadsheet to track any custom units you own on the Custom Unit Collection sheet. The current VC and C3G rosters are entered here. You can add any other custom figures to the bottom of this sheet. (Make sure to add the number of figures in that unit to Column M). If you have purchased figures but don't have a unit for them yet, you can also add them to this sheet. They won't be added to your "Roster" until you include them in the Combined Roster.

The Summary sheet will give you data on your entire collection, including a listing of all the units and sets you own in HCode format.

Pricing
The Pricing sheet contains the price data for official sets, units, terrain, and pieces, from eBay, Auggie's, and other fine Heroscape-selling establishments.

Unit and Army Tools
The Official Unit Roster sheet has information on all the official units, so you can sort, filter, and crunch numbers to your heart's content. The Combined Roster sheet has the same data, but also includes VC, C3G, and CUC units. The Unit Details sheet gives information about each Unit, including the stats and the special abilities, and a plain-text version of the same information suitable for custom posts on Heroscapers. The List Builder enables you to make a list of units based on multiple criteria.

The "VC" (Valhalla Customs) units are custom units designed to integrate with Classic Heroscape. (Formerly known as "SoV" and "C3V", those two custom lines have joined forces.) For more information, go to: http://www.heroscapers.com/community...splay.php?f=75
The "CUC" (Competitive Unit Congress) units are re-designs of official units, changing stats and/or powers to make them more competitive. For more information, go to: http://www.heroscapers.com/community...ad.php?t=27657
The "C3G" (Comics Customs Creators Guild) units expand the Superheroscape universe begun (but alas, not continued) by the Marvel set. For more information, go to: http://www.heroscapers.com/community...ad.php?t=28312
The "Delta / Delta+" system is an alternative unit cost system, to make units more balanced. "Delta" is used for armies with only official units, while "Delta +" is used for armies that also contain VC units. For more information go to: http://www.heroscapers.com/community...ad.php?t=46703
"Where available, each unit has information on it's ""Power Ranking"" and ""Color Scheme"". The former refers to the Power Rankings originally developed by spider_poison and Jexik. An explanation and listing is here:
http://www.heroscapers.com/community...ead.php?t=6171
A revised version, that includes the VC units (and their effect on the Official units) is mainted by dok here: http://www.heroscapers.com/community...ad.php?t=37772

The ""Color Scheme"" of units refers to a categorization of unit types (""Menacer"", ""Cheerleader"", etc.) developed by Jexik, and useful for thinking about army balance. A full explanation is here:
http://www.heroscapers.com/community/showthread.php?t=12071"

How To Add Your Customs
Adding your own customs to the spreadsheet is easy. First, you'll need to add them to the Custom Unit Collection page. Place them in the last rows available. All you will need to add there is the unit name, what figure you used, how many and for how much you bought them, and how many figures (1 for Heroes, or # of Squad Members) are in the unit. Put an "O" (for Other) in the System column.

The next step is adding the custom to the Combined Roster. Again, put your custom in the last available row, and fill out the information just as you see the other units. You'll want to create a UUC, a unique "airport code" so you can quickly identify that unit in the HCode for that army. Start it with a lower-case "o" and add 2-5 letters or numbers. Also, if you put the text of your unit's Special Abilities in the appropriate columns, the Unit Details will be able to pull that up, so you can have an easy-to-print reference card for the unit. You should also indicate if the unit has a d20 ability or any markers. If your unit has a Special Attack, you should put in the maximum Range and Strength of that attack. If your unit doesn't follow one of the classic 'Scape generals, you can put its "Alignment" (Good, Neutral, Evil, Unknown) into the Alignment column. You can also put in the link to the card's picture (or the figure's picture) in the Card or Figure Image URL column. As a bonus, the "Single Cell Writeup" column will extract all the data you've put in, in a format (almost) ready for posting on Heroscapers.com!

If you need to re-sort the Combined Roster, use the following sort order: Sort Order (smallest to largest), then Unit Name (A to Z).

The Army Builder
The Army Builder has been revamped. You can draw from the combined roster to create your army, or just choose from the Official figures. You can also use the Delta / Delta+ system for point values. And, you can now "save" an army using the "HCode".

The HCode lets you "save" an army set up in a short text format. The Army Builder worksheet will generate the code for any given army. The format is both machine and human readable. At a glance you can see the name of the army, how many points / hexes it has, and (with some practice) which units are in it. You can save the code a text file, post it on Heroscapers, etc. If you save it in the Saved Armies tab, the spreadsheet will extract the unit names and the army data. You can store, sort, and filter your armies -- so if you're looking for that 500-point army with range, VC units and an "evil" theme you made, you can find it. You can also take notes and record wins / losses. There's also an Army Printout where you can put four HCodes on one page in a format suitable for printout. There is more information on the HCode Instruction tab.

Other Stuff
The Special Powers sheet has the text for all the special powers on official, VC, C3G, and HoSS cards. They are also categorized, using the system developed by SirGalahad for the C3G Sortable Index.

The MAMA sheet has information on using the M/A/MA Order Marker Rules for large games. The red tabs are used to generate data throughout the workbook. Mess with the red tabs and Hot Lava Death awaits you.

For consistency, the following definitions are used. "Unit" is whatever comes with an Army Card. "Figures" are miniatures. (So, the 10th Regiment is one Unit with four Figures, and owning the Dund Figure means you own the Dund Unit and the Dund (CUC) Unit.) "Terrain" is anything with a hex. "Piece" is everything else (Fortress pieces, Trees, Glyphs, Obstacles, Dice, Markers).

What If I Don't Care About….
"What if I'm not interested in [CUC] [C3G] [VC] etc.?" Simple! You can just delete those rows from the Combined Roster, and they won't show up in the Army Builder, etc. I'd encourage you to make a backup version first, in case you change your mind later! You can also filter them out and you won't see them. If you're using the Army Builder, select "Official" from Army Unit Pool and you won't see any customs.
Acknowledgements
Bixby for the original version up to 9.
LeftOn4ya for versions up to 11, and for C3V data set.
Nomad for updating info on figures for wave 9-D13 and updating power rankings.
MegaBunnySuicide for pricing data.
Porkins for uploading the spreadsheet.
Spuke Boy for the database dump of custom units
Xorlof for help with the HCode format
SirGalahad for the C3G Sortable Index
Spidey'tilIDie for the World and Alignment data on C3G figures
Auggie's Buy and Sell Site for prices
eBay for rasing the prices to what the most obsessive collector will pay
Everyone at Heroscapers for keeping this thing going!

Release Description of Changes
Version 1.3 Added a name to the Army Creator
Version 1.3 Added a column to the roster that links to Card images.
Version 1.3 Added a column for Strategy notes to the Roster.
Version 1.3 Added a column for BIO information on the Roster.
Version 1.4 Added a column to the roster that links to HeroScapers Discussion.
Version 1.4 Added a cell in the Roster that contains path to directory for card images.
Version 1.4 Added a sort Macro to the Roster to control how the units are sorted.
Version 1.5 Added the Power Rankings to the Roster and they carry over to Unit Details and the army creator. (Power Rankings based of discussions on www.heroscapers.com).
Version 1.6 Added the scenario information to the control tab and the Scenario Creator tab.
Version 3.0 Removed the hyperlinks to card images and discussions and replaced with links to official "Books of HeroScape" discussions on heroscapers
Version 3.0 Added a number of the Unit BIOs to the Roster Tab.
Version 3.0 Added a number of pieces of information to the Unit Details Tab that get pulled over from the Roster Tab.
Version 3.0 Linked many of the Units on the Roster tabe to the appropriate product release form Hasbro.
Version 3.1 Fixed a problem with the SORT Macro. Sorry about that in Version 3.0
Version 3.1 Added the remaining unit BIO information from Official Hasbro site.
Version 3.1 Added product release information to each unit.
Version 4.0 Added Wave 7 Information
Version 4.0 Added MarvelScape Information
Version 4.0 Added 2007 Promotional Unit
Version 4.0 Added space for four special abilities on a card to accommodate Sonya Esenwein
Version 4.0 Added a number of links to the Weblinks Tab
Version 4.0 Added capacity in Unit Details to show 4th Special Ability
Version 4.0 Added the M/A/MA Rules Matrix to The Special Abilities Listing
Version 4.0 Added the new categories and values to the STATS tab.
Version 4.0 Added the List Builder Tab for creating Custom Unit Lists based on selection criteria
Version 4.1 Moved the BIO information the the Control Sheet as a lookup table.
Version 4.1 Changed Special Markers to actually list which special markers are used.
Version 4.1 Added Special Ability Selection to the List Builder
Version 5.0 Many Changes - Change log will now officially be at the above link.
Version 5.2 Fixed a problem with the List Builder, (Columns not properly aligned with Headings)
Version 5.2 Added all of the Swarm of the Marro information
Version 5.2 Added additional product information in Control Tab for later development
Version 5.2 Added LIFE to the Sort options on the Roster Tab.
Version 5.2 Added MOVE to the Sort options on the Roster Tab.
Version 5.2 Added RANGE to the Sort options on the Roster Tab.
Version 5.2 Added ATTACK to the Sort options on the Roster Tab.
Version 5.2 Added DEFENSE to the Sort options on the Roster Tab.
Version 7.0 Major overhaul and rewrite. (Simplied for future updates).
Version 7.0 Added Wave 8 Information
Version 7.0 Added Wave 8.5 Information
Version 7.0 Added Ticalla Jungle Information
Version 7.0 Fixed all of the Links for the "Books of HeroScape"
Version 7.0 Fixed WebLinks Section to update links for www.heroscapers.com discussions.
Version 7.0 Removed Sort and Sort Macros.
Version 7.0 Added all available Bio information.
Version 7.0 Deleted ALL named ranges and created new ones with standard naming convention.
Version 7.0 Changed the colour scheme of the sheet to be less, well, less pea soup green.
Version 9.0 Added Wave 9 Units to the Roster
Version 9.0 Added Wave 9 Information to the Control Tables
Version 9.0 Added Links to "Books OF HeroScape" for Wave 9 Units
Version 9.0 Added a Quantity Owned column to the Roster
Version 9.0 Added New Personalities, Classes, & Species to Stats Tab for Wave 9
Version 9.0 Updated the Power Rankings for all units up to Wave 8
Version 9.0 Updated Product Tab with detailed contents, MSRP, and Bar Code product numbers
Version 9.0 Added Collection Tab with basic analysis.
Version 10.0 Added Wave 10, BftU Master Set, D&D Wave 1-3 Information
Version 10.0 Updated Power Rankings
Version 10.0 Updated List Builder, Stats, Graphs worksheet, validation tables, formatting
Version 10.0 Updated collection sheet to better calculate quantity owned and needed
Version 11.0 Updated Errata for Wave9+, added nicknames, fixed misc errors
Version 11.0 Added Auggie's buy/sell & eBay prices for figures & tearrin, better collection calculator
Version 12.0 Redesigned, moved summary to new tab
Version 12.0 Changed calculation of hexes owned / wanted to allow for individual pieces bought / desired
Version 12.0 Added ability to track your own expenditures, sales, and trades, for both sets and individual units / pieces
Version 12.0 Added "Standard Collection" quantity, as separate from your individual desired quantity
Version 12.0 Added Eberron to list of Worlds, fixed units that should be from Eberron, fixed some other data issues
Version 12.0 Changed "The Einar Imperium" and "The Axegrinders of Burning Forge" to "Einar Imperium, The" and "Axegrinders of Burning Forge, The"
Version 12.0 Added data on "Ranged" attack to include special abilities that add a ranged attack
Version 12.0 Added "Owned", "Ranged", and "Unit Cost Range" to ListBuilder options
Version 12.0 Moved all pricing data to new worksheet "Pricing"
Version 12.0 Created Custom Unit worksheet
Version 12.0 Fixed errors on "Unit Details" worksheet
Version 12.0 Added a "Unique" check on Army Builder (name will turn red if you attempt to draft more than one Unique)
Version 12.0 Added an "Owned" check on Army Builder (quantity will turn red if you draft more than you own)
Version 12.0 Added ability to use custom units in Army Builder. Added "Other" to General chart to accommodate non-canon generals.
Version 12.0 Fixed calculation of average m/r/a/d on Army Builder to weight by number of units in your army
Version 12.0 Updated eBay + Auggie's Price data, and data for new sets. Thanks to MegaBunnySuicide for research.
Version 13.0 Added "Alignment" (Good, Evil, Neutral) to official, CUC, and VC units in Roster
Version 13.0 Added "Color Scheme" to Army Builder. Fancy conditional formatting, too.
Version 13.0 Added "Partial Value" (value of 1 Squad figure, or 1 Life of a Hero) to Roster
Version 13.0 Added "Trade List" to Individual Collection page, fixed some formula issues
Version 13.0 Added checks for Range and Flying to Roster.
Version 13.0 Added Collection UUC Summary
Version 13.0 Added Delta / Delta + pricing to Roster
Version 13.0 Added dok's Revised Power Rankings (VC-inclusive) as well as spider_poison / Jexik's original Power Rankings.
Version 13.0 Added Jexik's "Color Scheme" to official and VC units. CUC units assumed to have the same as their normal counterparts.
Version 13.0 Added plain-text version to bottom of Unit Detail page, for easy posting of custom units.
Version 13.0 Added Special Attack, SA Range, and SA Attack to Roster.
Version 13.0 Added Special Power text for VC units
Version 13.0 Added System Selector to Army Builder (so you don't have to see C3G / VC etc if you don't want to in the pull-down menu)
Version 13.0 Added UUC code to Roster
Version 13.0 Created HCode, Saved Army tab, Army Printout tab.
Version 13.0 Created Maps tab (beta)
Version 13.0 Fixed Summary page formula issues.
Version 13.0 Fixed Unit Detail page, added stats
Version 13.0 Imported CUC, VC, and C3G units into a new Combined Roster
Version 13.0 Imported figure data and wave data for VC and C3G units
Version 13.0 Imported Special Powers data from C3G. Added Official, VC, and CUC powers. Coded those powers. Added an "AT" code for powers that enable Additional Turns.
Version 13.0 Overhauled Army Builder page. Fixed stats. Added Delta option.
Version 13.0 Removed Stats, Graphs, Release Analysis, and Official Personality / World / Etc. pages.
Version 13.0 Removed Wave and Product from List Builder, added Flying and Ranged. Most categories are now free entry instead of pull-down.
Version 13.1 Fixes to Special Power spreadsheet, fixed typos in Rosters
Version 13.1 Added column for "Other Damage Effect Range" for powers that can cause damage, have range, but are not a normal or special attack (like Poisonous Acid Breath) or for potential range increases (Javelin, Reach). Also a column for Max Damage Range (highest value of normal range, SA range, and ODER range) and Max Non-Normal Damage Range (maximum of SA and ODER range). Figured out ODER for C3G units. I didn't include effects that were defensive but might still cause wounds at a distance, since there's no way to calculate the range.
Version 13.1 Included ODER range in Army Builder
Version 13.1 Added new VC and C3G figures
Version 13.1 Added HoSS (Star Wars) figures and powers
Version 13.1 Removed Maps Beta sheet, hoping to get some traction on an online version instead
Version 13.1 Imported Argus pricing data to pricing sheet, which still needs straighening out.
Version 13.2 Added column to Combined Roster for Card Image (put in the URL), and a "Single Cell Write-Up" which will take the data from your custom and put it in a ready-to-post format. With some editing.
Version 13.2 Fixed an assignment error -- Blastatrons and Deathreavers were assigned to the wrong sets.
Version 13.2 "Added ""Faction"" and ""Role"" to the Combined Roster (see here for more info: http://www.heroscapers.com/community...ad.php?t=48542 )
As this is a WIP I didn’t fill all the info out, I just included the Samurai faction to give you an idea of what the columns can be used for. If anyone wants to input all the factions, let me know! There's also all the C3G factions... You can use this to make groupings of your own."
Version 13.2 Added a "Custom Creator" column to Combined Roster. If you're adding someone's custom to your collection you can use this to keep track of who made it.
Version 13.3 Added latest C3G figures and special powers.
Version 13.3 Some unused columns on the Combined Roster have been eliminated
Version 13.3 Special Attacks and d20 power detection is now automagic.
Version 13.3 Re-did the UUC codes, due to feedback that people wanted more easily-read codes. My apologies if you've already put in a bunch of Saved Armies. However I've created a macro so you can easily convert your old HCodes to the new ones. Download the "Hcode Converter" spreadsheet from the link at the top of this page if you need this.
Version 13.3 Fixed a number of things with the "Single Cell Writeup" on Combined Roster, for use in creating postings of your customs on Heroscapers.com. Put the URL of the image of either your custom's card, or of the figure, in the "Card or Figure Image URL" column. Put any design notes, synergies, etc. into "Custom Notes." Then you can copy the "Single Cell Writeup" cell and paste the results into a text editor. (You can do all your customs at once for your OP!)

You'll need to fix two things. First, do a global search-and-replace for doubled quotation marks ("") and replace with a doubled tilde (~~). Then do a global replace of single quotation marks (") with nothing (). Then replace the doubled tilde (~~) with a single quotation mark(").

The other fix you'll need to do is remove the extraneous blank lines. If you're using Notepad++, go into TextFX > Edit > Delete Surplus Blank Lines, if you have a lot of customs and don't want to do them all by hand.
Version 13.3 Removed "World" as an option in ListBuilder, and replaced with "Alignment" using the Good / Neutral / Evil classification. You can put "G" to get all Good units, or "GN" for Good + Neutral units.
Version 13.3 Created a "Lite" version that does not include army tools.

To Do Update: Ongoing Auggie's & ebay prices, Product Barcode # for Wave9+
To Do Add: Weights for sets, terrain, & figures
To Do Synergy?
To Do Add trade list generation for custom units
To Do Glyph data (official and custom)
To Do DO data (official and custom)
To Do Add Strategy classes (Bishop / Rook / Etc) and links to strategy articles
To Do Extract which units use which powers for Special Powers page
To Do Stats to Extract: Attacks Per Turn, Stealthiness
To Do Add a "Do I own all the units for this army" criteria for the Saved Armies tab