Recommend
6 
 Thumb up
 Hide
15 Posts

Battlestar Galactica: The Board Game» Forums » Play By Forum

Subject: Modding with EXCEL Spreadsheets. rss

Your Tags: Add tags
Popular Tags: [View All]
Gerry Smit
Canada
Toronto
Ontario
flag msg tools
badge
Avatar
mbmbmbmbmb
Moving the discussion from the main Moderator thread to here.
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Gerry Smit
Canada
Toronto
Ontario
flag msg tools
badge
Avatar
mbmbmbmbmb
http://www.boardgamegeek.com/article/6000217#6000217

Copied from :


Yeah, I know. "Start an Excel Thread". I suspect I will, or a geeklist with instructions and training manual. However, until then, here are a few screen-shots of the coming-soon Excel Spreadsheet that handles Base and/or Pegasus, 3-7 players. (No, Exodus is NOT part of this, except where "future use" and "room for bigger lists" are being made.

Game State (where it all begins)

Nearly ALL of the PLAYER info is now entered on this worksheet, as implied with the Yellow cells. A lot of this info used to be entered on Destination and was repeated here. Destination is getting busy, so I removed it from there. Some important points. You select the type of Game : B-Base, P-Pegasus. Also destination is repeated here. If I'm moderating more than one game, plus playing in another, I'm constantly forgetting where we're headed.

Scroll Right to see:

You won't be over here that often. Get some character photos, load them in, and that's about it. The Character Names are a list, with some "Future Use" slots. Wanna playtest with Hot Dog & Cottle? Go ahead!

On the the Destination Worksheet:

Not a LOT of changes here, but important ones. All Player info is gone. The only CHARACTER info here is their location. The NUKE count is gone (did you notice it above?) There's more room for more Destinations.

The Ship charts haven't changed much yet. I suspect Exodus and Mark VII Vipers will cause bigger ripple effect here than removing the Nuke Counter. If you scroll right, there's a whole bunch of ship names ready to be used instead of just A, B, C, etc.

Scrolling DOWN on Destination gives you:

This is your virtual Board. Basically any Character Token, Damage Token, or Ship miniature can be noted in any of the Yellow Cells here.

A note about "MAIN BOARD SPACE SECTORS". The Summary column is like all the other yellow cells. The remaining columns drive the "Text-based Board Forum posting" and the "Icon-Based Board Forum posting". You only use them if you want to generate the Text or Icon boards in lieu of producing Photoshop images. THOSE Board Images and Forum postings were written by Danath. I am rolling his code into this spreadsheet this week, so that we get to a single spreadsheet just before Exodus release.


I've also revamped the Forum Token Generator . This worksheet really acts a guide to the moderator so that all steps of a turn get caught.

The spreadhseet is NOT as crisp and clean as the others, my workings aren't as crisp and clean as Brian's. Basically, anything automated for you to cut-n-paste into a Forum posting is located in a bright blue box in Columns G&H. Anything else around is supporting work/logic areas. (I'm switching to medium sized. Click on the photo and follow thru for expanded blow-ups if you need high detail).


Top of worksheet :

A repeater of character info, and a place for you to add and select what Moderator Banner you want.

Start of Turn Posting:

Notice the BLACK cell with Yellow Lettering? These cells contain IMPORTANT messages from me (well, the spreadsheet) to YOU the moderator.

Also, that "Sabotage a Treachery Discard" Interrupt token above turns blank if you've selected a "B" Base game only.


Die Roll Token for Die Rolls initiated by Actions:
The Yellow Columns to the right let you specify what Die Roll attempt is being made.

Skill Check Tokens for checks initiated by Actions:
There are three tokens now:
- interrupt
- interrupt the interrupt (for Support the People)
- resolve the check.

Support the People's Interrupt of Interrupt is blanked if it's Base only game.


Announcing the Crisis:


The above image is for Baltar's Crisis, the one below for Roslin.
You'll see that it prompts Baltar for his card selection.
Below it warns AGAINST posting, as it should be geekmailed to Roslin for her choice.



Then we have Die Roll, Skill Check Token generation again. This time tuned for those caused by Crisis Card draws, NOT actions. If it's Adama's turn, it will remind the players all '1's are positive. (Something that does NOT apply to Action Skill checks above!)



Finally:
post crisis, Activation, Jump Marker, and end of turn postings:

Not a lot of automation for post-crisis, it's too variable.
Activation & Jump progress are pretty straight forward.

End of turn includes prompts for Tigh to discard on a single card, and other players to discard if over 10 skill cards. (or Chief on 8 cards). (I can't remember if the Quorum discard over 10 is in there or not).
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Gerry Smit
Canada
Toronto
Ontario
flag msg tools
badge
Avatar
mbmbmbmbmb
GerryRailBaron wrote:
All of the above is so that I can ask you all for Feedback. What in the above seems likely to be okay, or to be not okay, etc. What's missing, badly needed, or just nice to have?

I'm thinking a bit of feedback now will save a lot of re-releasing of the spreadsheet NEXT WEEK. (yes, it'll finely be done!)

Anyway, Merry Christmas!

Gerry
Danath wrote:
I think it looks better then what I'm currently using.

I like the changes you've made. The other nice thing is I don't think adding Exodus will be much trouble. The worst part for me will be adding the extra board and creating the formulas for that... but it might be easier then I'm thinking.

In fact once your done you could quickly create space and even add some of the details in now (characters).
TerminusEst wrote:
Gerry, how are the different card sets handled with the different expansions? Will you have to delete the Pegasus (and Exodus) cards when running a base game or is this handled automatically?

One of my biggest sources of error when I first started modding was the various decks getting into an inconsistent state due to not being able to cut and paste the cards when dealing them out. I'd copy and paste the cards and then forget to delete them whence they came. This annoyed me sufficiently that I wound up heavily modifying the formulas to use =INDIRECT() and =ADDRESS(). I can now cut and paste the cards with wild abandon.
Danath wrote:
Pegasus is highlighted a different color so you delete them before a base game. I would imagine Exodus to be the same.

As for the cutting and pasting I bet your idea would be perfect. The most recent version I have you have to copy and paste and then delete which is annoying. But Gerry could have fixed this.
GerryRailBaron wrote:
You manually blank them out. The skill deck checker sets the expected total based on the B or P setting. Pegasus additions are going to be shaded Light Blue, Exodus additions will be shaded Light Green. Right now the Pegasus cards are in italics, and possibly shaded Ivory / Light Yellow.

But I'm trying to reserve shades of Yellow for "input field". Bright Yellow for frequent changes, duller yellow for less frequent/rare/once game changes. (Hmm, have to retouch the colours on Game State then, you don't change Userid, name or time zone that often, nor character.

AS far as cut n paste go, I've got an idea as to what you refer to. If I CUT and PASTE skill cards, the card counter goes nuts. I've long ago stopped doing that, and instead COPY, PASTE, scroll back and DELETE. Some of THAT was due to my heavy handed keying. When you cut-n-paste, anything you do next may auto fill or auto blank the fields you just used, filled or whatever. So to keep it simple, I copy-paste-delete. I'm guessing that there's a worksheet setting that will eliminate the above behaviour and that's what you found?

Expandability to Exodus.

Based on a preliminary reading of the published rules, and some of the forward-looking design that I started rolling in a couple of months ago, much of Exodus will just "drop into place". So within a few weeks I'll have a unified BSG 3-7 player spreadsheet. Heck, I've already got the moderator representation of the cylon board in "Destination".

I suspect the worst of it may be auto tracking Cylon Fleet in X2.

AS far as X1 and X3 go, fiddling with Loyalty Cards and Agendas is a lot of work for little automated gain. So initially it'll be just "list them by title" and let you all do it manually. We'll want a few spaces on the Loyalty pages that automatically map to the Geekmsg Mail generator.

X3's Allies & Trauma will be brand new so tracking that will take some coding. Again, step 1 is just get them all listed, and add them to the "here's your hand" geekmail. (This just beat out tracking Cylon Fleet for "worst of the work").

That's about it for now. I've got two saving graces going on: The release date is next week (earliest) and "players have to own it to PBF" so Exodus will be needed soon, but not NOW. Whereas the changes I'm showing off above are due, oh, last month?

Anyway, I think I'll get an Excel THread started here in PBF, repost my screen shots, and post the thread here. As others have said, let's not hijack the thread on technical details.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Noel Yap
Philippines
Pasig City
flag msg tools
Avatar
mbmbmb
The spreadsheet I'm currently using is based on Gerry's Pegasus sheet, but very heavily modified. It uses some very arcane Excel tricks to allow the user to CUT&PASTE cards between decks, rather than the error-prone COPY&PASTE then DELETE. While I was doing that, I threw in some other goodies like automated Skill card parsing for Skill checks, taking Adama's ability into account.

I've sent it off to Gerry if he wants to incorporate my changes into his sheet. It will likely be a non-trivial exercise, since I pretty much changed every single formula.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
J Chav
United States
Taylorsville
Utah
flag msg tools
Avatar
mbmbmbmbmb
Cut and paste would be AWESOME!
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Noel Yap
Philippines
Pasig City
flag msg tools
Avatar
mbmbmb
After Christmas, I'll post a tutorial on how it's done. In the meantime, I can send my spreadsheet to anyone who's interested. Drop me a Geekmail with your email address.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Gerry Smit
Canada
Toronto
Ontario
flag msg tools
badge
Avatar
mbmbmbmbmb
I Was just reading thru it a bit. Thanks for sending it!.

What's the difference between an ".xsl" file and a ".xslx" file? One of Brian's original intents was to avoid over-specializing the Excel. That is, make it backwards compatible with older Excels, not just Excel 2007. Also, his work has a higher security default, and so no macros. That's what I'm trying to work within.

What I'm going to do is :
1) FIRST get danath's Text and Icon displays working
2) get MY photoshop layer change list working
3) Exodus additions.

(2) & (3) are likely going to be in reverse order.

After that I'll try rolling your extensive changes in, but only if they won't break the guidelines of "older Excel" and "no macros".

Gerry
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Allan Cybulskie
Canada
Ottawa
Ontario
flag msg tools
Avatar
mbmbmbmbmb
If you haven't tried it with Google Docs, I can try it out and let you know. That would allow people who don't have Office to still use it.

 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
J Chav
United States
Taylorsville
Utah
flag msg tools
Avatar
mbmbmbmbmb
Daimbert wrote:
If you haven't tried it with Google Docs, I can try it out and let you know. That would allow people who don't have Office to still use it.



This is what i use. It appears to work fine.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
J Chav
United States
Taylorsville
Utah
flag msg tools
Avatar
mbmbmbmbmb
I take it back. I had one issue. The 'hide' function only visably hides it and if you copy and past (say to geekmail) it copies all of hte hidden stuff too. But that's minor.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Noel Yap
Philippines
Pasig City
flag msg tools
Avatar
mbmbmb
GerryRailBaron wrote:

After that I'll try rolling your extensive changes in, but only if they won't break the guidelines of "older Excel" and "no macros".

Ah, yes, my spreadsheet is developed on Excel 2007. There's most definitely no macros on it. I ran the compatibility checker on it and, aside from a stray named range that still referred to an external spreadsheet (grumble), the only other issue seemed to be that some cell formats may not be fully compatible. As an experiment, I saved the spreadsheet in the older compatible format. The latter seems to be OK, but without access to an older version of Excel I can't fully test it out.

My spreadsheet does actually load into Google Docs. There are some odd thing displayed and it's pretty slow. A lot of it actually looks fine, but cutting and pasting some cards produced an error flag on the sheet.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Gerry Smit
Canada
Toronto
Ontario
flag msg tools
badge
Avatar
mbmbmbmbmb
The hidden rows were there as back-up during development. They weren't needed for a back-out, and so have been removed.

Gerry
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
David F
United States
Emeryville
California
flag msg tools
Luck in games, in measured doses, is the catalyst which enables shocking game-changers that you'll remember and talk about forever.
badge
Let the Lord of Chaos rule.
Avatar
mbmbmbmbmb
GerryRailBaron wrote:


What's the difference between an ".xsl" file and a ".xslx" file? One of Brian's original intents was to avoid over-specializing the Excel. That is, make it backwards compatible with older Excels, not just Excel 2007. Also, his work has a higher security default, and so no macros. That's what I'm trying to work within.

What I'm going to do is :
1) FIRST get danath's Text and Icon displays working
2) get MY photoshop layer change list working
3) Exodus additions.

(2) & (3) are likely going to be in reverse order.

After that I'll try rolling your extensive changes in, but only if they won't break the guidelines of "older Excel" and "no macros".

Gerry


.xlsx is only for Excel 2007 while .xls is Excel 2003 or older. There were huge improvements made between Excel 2003 and 2007, unlike the other Office programs.

What's the problem with cut & paste? If it's just screwing up card counts because of the =COUNTA function, you just need to leave a blank row on the first row and last row of your counting range.

e.g.

HAND SIZE: 8 (=COUNTA(everything below))
(blank row)
4 - Investigative Committee
3 - Declare Emergency
1 - Executive Order
1 - Executive Order
1 - Executive Order
1 - Executive Order
1 - Executive Order
2 - Consolidate Power
(blank row)

With the above setup, you're free to cut & paste anywhere you want without screwing up the card count.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Gerry Smit
Canada
Toronto
Ontario
flag msg tools
badge
Avatar
mbmbmbmbmb
Beta Version of Excel spreadsheet now available. It supports Base Game and Pegasus, 3 to 7 players. It has some obvious expansion locations where I will roll in the first part of Exodus.

It does not yet have the automatic imageid add-ins for Actions and Crisis cards.

Please geekmail or post any bugs you locate. Please feel free to geekmail me with any questions. If you have updates for it, let me know that to. Danath and I have been working to include "Text" board and/or "Icon Board" generation. That is MISSING from this version, but will be there within a week. The last MAJOR feature I HOPE to include is a worksheet that has 1 row per layer of Photoshop file. That is NOT yet there either.

BSG BaseOrPegasusNPlayer.zip



As well, the photoshop files have been updated. The links remain the same but the underlying files have been "improved". I've added more style layers, many of which you turn off until needed. Example: Tigh's Whiskey Bottle where his "x0" skill card count would go.

I'll repeat the file links here, and then go update them on the wiki.

Base Game Board Photoshop Support File

Pegasus Game Board Photoshop Support Image
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Noel Yap
Philippines
Pasig City
flag msg tools
Avatar
mbmbmb
selwyth wrote:

What's the problem with cut & paste? If it's just screwing up card counts because of the =COUNTA function, you just need to leave a blank row on the first row and last row of your counting range.

That is, IMHO, too dependent on the moderator actually remembering to leave a blank row when cutting and pasting. When you're juggling cards between multiple Skill decks, discard piles, players hands, the Destiny deck, and the Skill check, that's just a situation where a simple mistake can lead to an inconsistent mess of a spreadsheet. The mod already has a pretty high workload without having to debug the whereabouts of a stray card.

I worked out what I consider to be a more reliable solution using the =INDIRECT() function. This hides the address range from Excel's tracking mechanism, so you're free to cut-n-paste without worrying about maintaining blank rows.

Here's a list of changes I made to Gerry's spreadsheet, copied straight from the email I sent to Gerry. If you don't have my version in hand, some of it won't make sense, but it will give you an idea of how extensive my changes actually were:
Quote:

# Ability to CUT & PASTE cards between various decks without messing up the card counts. This accomplished by some very arcane Excel formulas. Look at Sheet 'Skill Cards' cell B2, which gives the number of cards currently in the Politics deck. It has the formula =COUNTA(INDIRECT(ADDRESS(ROW()-26,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))). Any of the light green-shaded cells, which give card counts, are formulated like that. It's not pretty but it works.

# Use of lookup tables, rather than giant =IF() formulas. This is even more arcane Excel. Look at Sheet 'Game State Summary', cell D14. This gives the name of the player currently selected by the 'X' in the character selection table. There are other places that use this, such as 'CrisisLib'. Any place shaded dark green should use this feature. Note that CUT&PASTING the 'X' doesn't work too well on these tables, mainly because you also lose formatting. I'm not too bothered by this since it's just a single 'X'.

# Defining named ranges. Rather than trying to remember that the current Politics card count is in some particular cell on some particular worksheet, I can just put in "Politics" into a formula. Any of the salmon-shaded cells will work like that, such as D18 on 'Game State Summary', which gives the size of the current Destiny deck, based on the value elsewhere in the workbook.

# In the 'Skill Cards' sheet, the Skill check table will automatically parse the value of each card entered into the check. There's no need to enter the values manually. It also takes Adama's ability into account if he's the current player.

# The way I've set up the various decks, you should be able to put the cursor on the 'Sort' heading and then just click on the [Sort] button to shuffle the deck; no need to highlight the whole table.

As I already said above, there's no macros involved; the automation is all formula-driven. While I developed it in Excel 2007, it should be compatible with previous versions.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.