Reply to thread

Message

<blockquote data-quote="Nytmare" data-source="post: 6053902" data-attributes="member: 55178">I'll give you an example from one of my older 3.5 character sheets.Hit points are just a number.&nbsp; I could just have a cell where I type in the number, but I want to be fancy cause I'm a glutton for punishment.So first off, the sheet itself isn't where I type numbers in at all.&nbsp; I never know when I'm going to screw up and accidentally type over something important, so I make a separate form on a separate sheet in the workbook, where the only thing I'm doing is typing stuff in.&nbsp; I'll even go so far as to make all the cells where I want to type stuff in light green in case I get extra stupid.Now, so that I can have a record of what choices I'm making when (and to make sure that I don't do something like gain hitpoints more than once by mistake)&nbsp; I'll have the form be set up so that I can input what choices and changes are made each level (like how many hitpoints I rolled) and have the sheet add those as well as auto-calculate how many hit points the character will gain from their con bonus.Buuuuut, I still want to be a pain and add more.&nbsp; We use a Toughness house rule that gives you 1 hitpoint each level from the point where you take the feat on.&nbsp; So now I want to add something in that looks for the word &quot;toughness&quot; in the &quot;Feat&quot; section, and then adds 1 hitpoint every level after that.&nbsp; Now it's getting more complicated, so I add a new column next to the part of the form and have it check two things.&nbsp; #1 it checks to see if the cell next to it has the word &quot;toughness&quot; in it.&nbsp; If it does, it makes it's value 1.&nbsp; Also it checks to see if the cell directly above it is 1.&nbsp; If it is, that means that something spotted the word &quot;toughness&quot; somewhere above, so again, it makes its value 1.&nbsp; To do this as a single formula inside a cell, it means that I have to have an IF statement with a 2 query OR, a single FIND, and an ISERROR of the possible failed FIND statement in it.Now, to be extra silly, I like to have checkmark bubbles for things like hitpoints and ammo.&nbsp; Granted I could do this by hand, but I'm enjoying the challenge, so I'm going to have a formula look at the number of hitpoints and use the REPT function to make 1 &quot;O&quot; for every hitpoint. I like it, but I'd like it even more if I could split them up into groups of 5, and the formatting is weird with line breaks, especially once hitpoints reach the 30 or 40 mark, so I'll split it across two different cells, and it would be nice if I could figure out a way to...In the end the formula for the two cells where hitpoint check bubbles are displayed is:=IF(ROUNDDOWN(DO14/2/5,0),REPT(&quot;OOOOO&nbsp; &quot;,ROUNDDOWN(DO14/2/5,0)),&quot;&quot;)&amp;IF(ROUNDUP(MOD(DO14/2,5),0),REPT(&quot;o &quot;,ROUNDUP(MOD(DO14/2,5),0)),&quot;&quot;)and=IF(ROUNDDOWN(DO14/2/5,0),REPT(&quot;OOOOO&nbsp; &quot;,ROUNDDOWN(DO14/2/5,0)),&quot;&quot;)&amp;IF(ROUNDDOWN(MOD(DO14/2,5),0),REPT(&quot;o &quot;,ROUNDDOWN(MOD(DO14/2,5),0)),&quot;&quot;)So yeah, totally my fault, but my character sheets when done straight with formulas meant that they were incredibly delicate, and they had a crazy learning curve that stopped me from being able to let my players fill stuff in for themselves.&nbsp; VBA let me pack stuff out of the way, let me program in an editor where I could use wordprocessor-esque commands, and gave me access to waaaaay more options and toys to pack into my sheets.</blockquote>

[QUOTE="Nytmare, post: 6053902, member: 55178"] I'll give you an example from one of my older 3.5 character sheets. Hit points are just a number. I could just have a cell where I type in the number, but I want to be fancy cause I'm a glutton for punishment. So first off, the sheet itself isn't where I type numbers in at all. I never know when I'm going to screw up and accidentally type over something important, so I make a separate form on a separate sheet in the workbook, where the only thing I'm doing is typing stuff in. I'll even go so far as to make all the cells where I want to type stuff in light green in case I get extra stupid. Now, so that I can have a record of what choices I'm making when (and to make sure that I don't do something like gain hitpoints more than once by mistake) I'll have the form be set up so that I can input what choices and changes are made each level (like how many hitpoints I rolled) and have the sheet add those as well as auto-calculate how many hit points the character will gain from their con bonus. Buuuuut, I still want to be a pain and add more. We use a Toughness house rule that gives you 1 hitpoint each level from the point where you take the feat on. So now I want to add something in that looks for the word "toughness" in the "Feat" section, and then adds 1 hitpoint every level after that. Now it's getting more complicated, so I add a new column next to the part of the form and have it check two things. #1 it checks to see if the cell next to it has the word "toughness" in it. If it does, it makes it's value 1. Also it checks to see if the cell directly above it is 1. If it is, that means that something spotted the word "toughness" somewhere above, so again, it makes its value 1. To do this as a single formula inside a cell, it means that I have to have an IF statement with a 2 query OR, a single FIND, and an ISERROR of the possible failed FIND statement in it. Now, to be extra silly, I like to have checkmark bubbles for things like hitpoints and ammo. Granted I could do this by hand, but I'm enjoying the challenge, so I'm going to have a formula look at the number of hitpoints and use the REPT function to make 1 "O" for every hitpoint. I like it, but I'd like it even more if I could split them up into groups of 5, and the formatting is weird with line breaks, especially once hitpoints reach the 30 or 40 mark, so I'll split it across two different cells, and it would be nice if I could figure out a way to... In the end the formula for the two cells where hitpoint check bubbles are displayed is: =IF(ROUNDDOWN(DO14/2/5,0),REPT("OOOOO ",ROUNDDOWN(DO14/2/5,0)),"")&IF(ROUNDUP(MOD(DO14/2,5),0),REPT("o ",ROUNDUP(MOD(DO14/2,5),0)),"") and =IF(ROUNDDOWN(DO14/2/5,0),REPT("OOOOO ",ROUNDDOWN(DO14/2/5,0)),"")&IF(ROUNDDOWN(MOD(DO14/2,5),0),REPT("o ",ROUNDDOWN(MOD(DO14/2,5),0)),"") So yeah, totally my fault, but my character sheets when done straight with formulas meant that they were incredibly delicate, and they had a crazy learning curve that stopped me from being able to let my players fill stuff in for themselves. VBA let me pack stuff out of the way, let me program in an editor where I could use wordprocessor-esque commands, and gave me access to waaaaay more options and toys to pack into my sheets. [/QUOTE]

Verification