Excel question:

[Oompa]

I am trying to create a combat sheet in excel, now i am such a noob that i can't get a formula to work.. (im a formula noob)

- Let's say i have a character with 35 hp, i want that to display in A1..
- Lets say he takes 10 damage, i say: 10 in B1 (after i press enter i want the 10 to disappear..)
- A1 changes to 25
- So now column A1 shows 25 and B1 shows nothing..
- Lets say my character spents a surge and heals 10 hp.
- I enter 10 in cell C1 and C1 becomes empty and A1 changes back in 35..

Rinse and Repeat.. oh and is it possible to make sure A1 never gets above 35?

Is this possible? Yes? How?

Oh and i ask it here.. cause here lurk the sheet wonder makers!

 

[beverson]

What you want can certainly be done, but not the simple way using only formulas...a little bit of VBA coding comes into play...let me fiddle and see what I come up with (been a while, my Excel VBA might be a little rusty!)

 

[beverson]

Ok, I threw this together...not "exactly" how I might do it given time to really think it through, plus I am at work so I was doing it in stealth mode.

In this example, you have your hit points in A1, damage in B1, and healing in C1. You have to click the appropriate button after entering damage or healing in the cells, and it will calculate for you. When the sheet first opens, it initializes the max hit points at 35 in the code. I don't know how familiar you are with coding in Excel, but if you want to see the code hit Alt-F11 and it will open the code window.

Oh, and of course it makes sure that hit points never get above 35.

 

[Oompa]

Thanks, This certainly helps me in the right direction

 

[beverson]

Thanks, This certainly helps me in the right direction

You're welcome...glad I could help.

 

[Nytmare]

There are things you might want to steal out of my combat tracker.

When something takes damage you just input it into the "damage" column (or negative damage to denote healing), and it calculates everything else out for you.

http://www.pmw.org/nytmare/dnd/software/Initiator.xls

 

[beverson]

There are things you might want to steal out of my combat tracker.

When something takes damage you just input it into the "damage" column (or negative damage to denote healing), and it calculates everything else out for you.

http://www.pmw.org/nytmare/dnd/software/Initiator.xls

Nytmare, when I try to open your file after downloading it, I get all kinds of crazy errors and ultimately end up with a corrupted workbook. Any thoughts on what might be the issue? I'd like to take a look at what you've done.

Oompa, for what it's worth here is a different take on the idea, where you click a button and it prompts you to enter the amount of damage or healing, rather than keying it into a cell...

 

[Nytmare]

It's Excel 2003, what version are you using?

 

[beverson]

It's Excel 2003, what version are you using?

I'm using 2003 as well...

 

[Nytmare]

Well, if the excel sheet is failing, here's the pertinent vb at least.

[sblock]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Row > 5 And Target.Row < 26 Then
Cells(Target.Row, 38).Value = 1
If Target.Value = None Then
Cells(Target.Row, 38).Value = 0
End If
Exit Sub
End If
If Target.Column = 7 And Target.Row > 5 And Target.Row < 26 Then
Application.ScreenUpdating = False
If IsNumeric(Target.Value) = False Or Target.Value = 0 Then
Target.ClearContents
Exit Sub
End If
newcolumn = Cells(Target.Row, 37)
If newcolumn = 37 Then
oldDamage = Application.WorksheetFunction.Sum(Range(Cells(Target.Row, 12), Cells(Target.Row, 36)))
Range(Cells(Target.Row, 12), Cells(Target.Row, 36)).ClearContents
Cells(Target.Row, 12) = oldDamage
Cells(Target.Row, 13) = Target.Value
If Cells(Target.Row, 11) > Cells(Target.Row, 10) Then
Cells(Target.Row, 14) = Cells(Target.Row, 11) - Cells(Target.Row, 10)
End If
Target.ClearContents
Exit Sub
End If
Cells(Target.Row, newcolumn) = Target.Value
If Cells(Target.Row, 11) > Cells(Target.Row, 10) Then
Cells(Target.Row, newcolumn + 1) = Cells(Target.Row, 11) - Cells(Target.Row, 10)
End If
Target.ClearContents
If Cells(Target.Row, 11).Value < 1 Then Cells(Target.Row, 38).Value = 0
Application.ScreenUpdating = True
End If
End Sub
[/sblock]


Basically:

• If the row has a "name" in it
• And if a number is entered in the "damage" cell of that row
• Add the damage to the end of the "damage dealt" range
• If the damage dealt range is full, add it all up and start a new damage dealt range
• If the "remaining hitpoints" number is bigger than the "starting hitpoints", make the remaining hitpoints the same as the starting.
• Clear the damage cell

I also have everything linked up to conditionals so that something at half HP turns red and something at 0 hps turns grey.

[EDIT] Huh, I just noticed that if I open up the file inside of Excel it opens fine, but if I try to open the file by itself it takes about 3 minutes to sort itself out... I wonder what's wrong with it.