Hi all
My 13yo son is having a play in Excel creating character sheets for his own D&D-type role playing game.
All the normal stats have gone quite well and everything is working well.

Link: http://www.thingswelike.org/Character-Sheet.xlsx

However he now wants to add spell calculations to the sheet to work out the damage done to monsters.
My knowledge of Excel is zero, so even knowing what to look up on google is proving difficult.

The formula for some of the spells are:
(D4= role of a 4-sided dice MP=magic points used Damage is in HP Hit Points)

Fireball ——– cost 5MP. Damage = 8+D4+MagAtt+1/2EnemiesDef
Boiling Breath cost 7MP. Damage = 10+D4+MagAtt+1/2EnemiesDef
Fire Pillar —– cost 9MP. Damage = 14+D4+MagAtt+1/2EnemiesDef
…and so on for 40 spells.

The damage is different if the spell class is opposite to the monsters (So use a Fire spell on a Water monster and the number in bold above changes to a higher number)

Ideally, he wanted a dropdown of all spells (organised by class) and when you selected one, it calculated the formula from the cells on the character sheet and all you had to do was roll the D4 to add on.

But we’ve run into problems knowing which character is casting the spell and which monster it’s attacking.
So it would be a few dropdowns:
Spell Name | Character number | Monster number | Opposite yes/no

Or something like that!

Any ideas?

If you know the character number and monster number surely you don’t need to know opposite? You have the class against each character I imagine, so maybe build another sheet of how the classes interact – dunno if it’s more complex than opposites but that is simple enough.

The class comes form the spell vs monster – so yes, you are correct that it could be calculated.

So do we create a table with columns
Spell Name | Class | Cost | Damage variable

Then somehow create a dropdown from that?

He’d be best looking at Pivot Tables with all the reference material for the game entered. It gets very complex but is useful to build up Excel skills – I found one for 4th Edition D&D a few years ago that had pages and pages of Pivot Tables and lookups where someone had spent hours entering every table from the rule books and expansions.

Is it his own design game or a pre-existing system? If it’s his own design then it may be worth suggesting a different approach.

Pathfinder for example usually has a Caster Check, so the character stats, skills, feat bonuses and spell level arrive at a number that the target has to roll to beat. The damage taken is then a dice roll + fixed bonuses, subtracting any restistances of the target.

The spreadsheet bit should really be used for what the static bonuses and resistances for a character are so you can create the character sheet, once playing the game it’s easier to just roll and add those bonuses. rather than expect the character sheet to do all the maths. Paper RPGs are oddly social things and using a spreadsheet to calculate all the attacks takes some of that social aspect away.

If it’s for an established game then look at Hero Labs. Pretty comprehensive character generator for several systems – can be a bit expensive if you want several game modules though.

Here’s the link to someone’s 4th Edition Sheet, just open one of the links on the left of the screen under Open 4E Spreadsheet.

http://dnd4.com/dd-4th-edition-character-sheet

The sheets are protected and all the tables hidden, you might be able to work out how that’s been done – although there are some really complex equations in there.

allan23 – Member

He’d be best looking at Pivot Tablesok – will do.

Is it his own design game or a pre-existing system?

His own game – it’s a simplified version we’re playing as a family.

Paper RPGs are oddly social things and using a spreadsheet to calculate all the attacks takes some of that social aspect away.

Agree – he just wants to do it, to learn. Also his mother hates the fights but loves the puzzly (zelda-influenced) dungeons so he’s trying to take a little of the pain away.

The sheets are protected and all the tables hidden

Ah! That will be why I didn’t think they had the same types of data. I’ll try and reverse engineer.

Thanks for your help!

However much I unhide/unlock I can’t see any tables in that doc.

grrrr – I’ll have a bit of time to investigate further tomorrow night.

If I remember the hidden tables are worksheets – one for Weapons, another for Magic etc.

I’d have to look up how to unlock worksheets in Excel now. Not had to do it for a bit so it’s been pushed into the unused corners of my brain.

I thought I found the unhide worksheet (it was next to Format > Unhide Columns & Unhide Rows), but it was greyed out, so I thought that meant there weren’t any to unhide.

Could be wrong.

Had a quick look, was on the road yesterday so couldn’t post, I suspect they’ve tied it down a little more as enterprising Excel Experts were hacking it apart.

If you go into the VB Editor you can see all the other worksheets with the hidden tab. Something in the security wouldn’t let me change the view setting for the tabs anymore.

I don’t have an old copy unfortunately.

The Pivot tables were on the Worksheets by theme and the equations in the cells referenced them for lookups. Might be worth looking up Pivot Tables for Excel in general and seeing if there are tutorials your lad can follow.

I’m used to installing and fixing Excel but using it is another matter – I usually call my accountant friend as accountants seem to be brilliant at over complicated spreadsheets 🙂

For game systems it might be worth getting him to make the combat less complicated rather than automating something complicated. Getting rid of bonuses and making attacks something like a dice roll per level might help keep the game flow faster.

Thanks for your help Allan!
Currently he’s chucked in absolutely massive formulas containing tons of if statements!
He’s really happy and to be fair the logic is all sound, it’s working and he’s learnt something.
But I can’t help wanting to do it right 🙂

All the extra worksheets are hidden, just enable the content which should allow you to un-protect the workbook/worksheet and right click one of the three available tabs at the bottom. There are around 24 hidden tabs. Can email you it if you want with everything removed.

That would be helpful. Thank you! I can see the sheets in VB, but haven’t managed to get them in Excel. I’m a complete novice in it.
Email in profile.

YGM – all the tabs are available now in the character sheet I sent. Have fun 🙂

Thank you very much for that.
Wow – that’s a lot of data!