INDEX and MATCH allow you to extend ranges (add columns and rows) to data tables, and search for the column and row by the actual header rather than by column or row number.
The format for INDEX is INDEX(range,column,row). You can use a named range for the range variable.
The format for MATCH is MATCH(lookup value,lookup range,match type). You'll want to use 0 (exact match) as the type.
You can use MATCH commands inside the INDEX command to return the column and row numbers - pulling that data from the actual text strings you use. So if you insert a column to the left of one in a table, it still points to the same data.
The reason why the dynamic lookup trick I gave causes a performance hit is because of the INDIRECT function. You may be able to get by with the CHOOSE function instead, if you only have 15 items to choose from. INDIRECT is a volatile function; every time something changes in the sheet, all INDIRECT functions are recalculated. A few (up to 100 of them) are probably fine - more than that and the sheet will get a tiny bit sluggish. (Doing conditional formatting will also cause the sheet to slow down.)
|
I am having trouble adding the Galorian specific feats (big game hunter et al). They come up red and I have no idea on what I am missing! By the way this sheet is superb as always. As I get the time to fiddle with it some more I will see about the areas to help you kick the awesome factor up a notch!
I just started checking out the product and I like what I see so far but I have found one flaw that should be very easy to fix. The melee weapon damage doesn't calculate in the strength modifier. The character I am using to check it out has a 15 strength and a +1 long sword but the only addition it gives for the damage is the +1 from the sword.
To the top right of the weapon block you select how much, if any of your strength modifier to apply. It defaults to zero. This way you can take into account wielding one hand weapons with two hands, offhand, etc.
I'd like the ability to specify that a weapon I've entered is not carried.
I'm also not sure how to specify how much ammo I'm carrying for something - this is useful for calculating encumbrance.
The problem with dynamic ranges pointing to wrong columns can be fixed with the INDEX & MATCH solution I posted about earlier.
Cell CE25 should have its formula updated to:
=MIN(0,VLOOKUP(BP22,Table_Armor,6,0)+IF(CT22="",0,1)+IF(OR(CM28="Mithral",C M28="Darkwood"),2,0))
(It doesn't check for Darkwood when figuring the skill rank penalty)
I'd like the ability to specify that a weapon I've entered is not carried.
I'm also not sure how to specify how much ammo I'm carrying for something - this is useful for calculating encumbrance.
The problem with dynamic ranges pointing to wrong columns can be fixed with the INDEX & MATCH solution I posted about earlier.
You should be able to put in 0 for weight on the Front page and it will keep weapons from being factored into encumbrance (ie, not carried)
i've just been entering ammo on the Back page in the Gear section
You should be able to put in 0 for weight on the Front page and it will keep weapons from being factored into encumbrance (ie, not carried)i've just been entering ammo on the Back page in the Gear section
I've not been able to find that option on the front page; the place where it's generating the weight is the result of a lookup table of weapon type and weapon size, and is normally protected (and has a formula to break if it's not protected).
I'm be tempted to create a "carried" yes/no drop down that, if set to "no" set the weight to 0. (This is useful for keeping the stats for, say, the crossbow you carry on your horse handy, without dragging your melee encumbrance down.)
Erian, if this makes sense to you, consider it a feature request for the next version. :)
You should be able to put in 0 for weight on the Front page and it will keep weapons from being factored into encumbrance (ie, not carried)i've just been entering ammo on the Back page in the Gear section
I've not been able to find that option on the front page; the place where it's generating the weight is the result of a lookup table of weapon type and weapon size, and is normally protected (and has a formula to break if it's not protected).
I'm be tempted to create a "carried" yes/no drop down that, if set to "no" set the weight to 0. (This is useful for keeping the stats for, say, the crossbow you carry on your horse handy, without dragging your melee encumbrance down.)
Erian, if this makes sense to you, consider it a feature request for the next version. :)
unprotect the sheet and set weight to 0.. it doesn't break anything. I agree a carried/not carried might be a good idea though. I had to set an entry's weight to zero because I wanted to have my flail on there twice with different bonuses but not get counted twice for encumbrance. Setting it to zero as in the link below worked fine
I created a Carried drop down that limits to Yes/No, and a quantity drop down that limits to 1 through 6 (for things like throwing javelins).
Both modify the weight of the weapon appropriately - if carried is set to "N", weight is 0. If the quantity carried is greater than 1, it multiplies the weight by the quantity carried.
Well, I'm obviously on the right track for my major updates in v.0.8...you guys are asking questions about all the focus areas (weapon, feat, and trait automation)!
I am having trouble adding the Galorian specific feats (big game hunter et al). They come up red and I have no idea on what I am missing! By the way this sheet is superb as always. As I get the time to fiddle with it some more I will see about the areas to help you kick the awesome factor up a notch!
Thanks! Those feats are coming up red because the prerequisite automation is not complete and so the Qualified field is blank. I'll had-code a TRUE in place until the automation is done and that will make the red go away.
I just started checking out the product and I like what I see so far but I have found one flaw that should be very easy to fix. The melee weapon damage doesn't calculate in the strength modifier. The character I am using to check it out has a 15 strength and a +1 long sword but the only addition it gives for the damage is the +1 from the sword.To the top right of the weapon block you select how much, if any of your strength modifier to apply. It defaults to zero. This way you can take into account wielding one hand weapons with two hands, offhand, etc.
Thanks, that's exactly correct. As I work on weapon automation, I do plan to auto-calc the Str mod based on hands used, feats applied, etc.
Hello,
Love the sheet by the way. Here are a few issues I found on the copy of the file I downloaded for PRPG_CS_v_0_7. I am using Excel 2003 if it makes a difference.
On the Customization tab.
The Custom Skills header position AD14 is mapped as "=D9", should be "=A9"
The Custom Skills header position AE14 is mapped as "=D11", should be "=A11"On the DataSkills tab.
The Custom Skills section is on line 53 and 54 skip the mappings for "=Z15" which is shifting the last few maps over 1. You go from "=Y15" to "=AA15"
Got 'em fixed, thanks!
Everything else so far looks awesome. The only thing I would like to see specifically added is if you could pre-build the skill roll for Concentration...though its not treated as a skill in Pathfinder, it follows basically the format as a skill roll.
This would be a little tricky, since a Concentration check changes based on factors (caster level + ability score modifier) that change depending on class. So, a Bard/Ranger actually has two concentration check values.
In DataSkills tab, your code for the custom skills section looks like its missing quotation marks in the if statements.
Got 'em fixed, thanks!
Cell CE25 should have its formula updated to:
=MIN(0,VLOOKUP(BP22,Table_Armor,6,0)+IF(CT22="",0,1)+IF(OR(CM28="Mithral",C M28="Darkwood"),2,0))
(It doesn't check for Darkwood when figuring the skill rank penalty)
Got it fixed, thanks!
I created a Carried drop down that limits to Yes/No, and a quantity drop down that limits to 1 through 6 (for things like throwing javelins).
Both modify the weight of the weapon appropriately - if carried is set to "N", weight is 0. If the quantity carried is greater than 1, it multiplies the weight by the quantity carried.
CARRIED and QUANTITY fields for the weapon blocks definitely make sense and I can work that into the next release.
Got a place where I can email you my modded sheet so you can cut and paste?
Got a place where I can email you my modded sheet so you can cut and paste?
Sure...
Sent.
You can edit your post to get your email addy out of it now.
Sent.
You can edit your post to get your email addy out of it now.
Thanks! I'll check it out this morning. I worked on rebuilding the weapon block last night--it now better supports different sizes from Fine to Colossal (although for Gargantuan and Colossal I'm unsure on some of the damage progressions and so they're blank). I put a CARRIED field right after the weapon name and it affects the Weight as discussed earlier. And the big one--I've removed the Ammunition line and replaced it with a set of Combat Option drop-downs. You can now select 2-Handed, Power Attack, Combat Expertise, etc. with up to five Combat Options affecting a single weapon. I've also built in the automatic feats--Weapon Focus/Specialization, proficiencies, etc.--so they auto-calc in the weapon entries. I'm still working on building out 2-weapon fighting, mounted combat, and weapon size vs. character size but should have those done today.
For Ammunition, I'm bouncing between moving it entirely to the Back sheet vs. removing one of the weapon stat blocks and using that space. Moving it to the Back is easiest, but also puts it a page-flip away from general combat info (which I've found leads to forgetting to track it). Any thoughts on this?
For Ammunition, I'm bouncing between moving it entirely to the Back sheet vs. removing one of the weapon stat blocks and using that space. Moving it to the Back is easiest, but also puts it a page-flip away from general combat info (which I've found leads to forgetting to track it). Any thoughts on this?
I'd say it needs to be on the front, myself. I'd give up the extra weapon block for it in a heartbeat.
While you're on the Power Attack automation, can you do anything with the Combat Expertise side of things and AC, like a drop down box that incorporates it into both the weapon blocks and AC?
I'd say it needs to be on the front, myself. I'd give up the extra weapon block for it in a heartbeat.
That's my leaning as well...any on the opposing side speak now!
While you're on the Power Attack automation, can you do anything with the Combat Expertise side of things and AC, like a drop down box that incorporates it into both the weapon blocks and AC?
Already done, actually...I'm incorporating Fighting Defensively and Total Defense as well.
Just remember that both Combat Expertise and Power Attack can be 'turned off' as needed. (I habitually make characters with both - Combat Expertise to combine with Dodge/Mobility to run up to the enemy wizard, Power Attack to put him out of his misery.)
Just remember that both Combat Expertise and Power Attack can be 'turned off' as needed. (I habitually make characters with both - Combat Expertise to combine with Dodge/Mobility to run up to the enemy wizard, Power Attack to put him out of his misery.)
Yep, they all work as drop-down selectors that you can change on the fly. I'm sure it'll need some good debugging to make sure everything is interacting properly (no Deadly Aim stacking with Power Attack silliness). I also had a "duh" moment and figured out I really shouldn't be making multiple calculations from tables over and over, but rather make the calc once on the data tab and the just name that cell for use in formulas. The cleanup is removing a lot of the VLOOKUP functions and has reduced the file size by about 200 Kb.
Just wanted to chime in with some applause for how great this sheet is. It's clear a ton of work is going into this for the good of the community. Kudos to you and all who are helping!
How about some Paizo recognition as the "Official Automated Character Sheet of the Pathfinder RPG" or some such? :)
Just wanted to chime in with some applause for how great this sheet is. It's clear a ton of work is going into this for the good of the community. Kudos to you and all who are helping!
How about some Paizo recognition as the "Official Automated Character Sheet of the Pathfinder RPG" or some such? :)
Heh, thanks! There are some other good works in progress as well (Kor is working on one for instance) and I'm not sure if any official Paizo folks have even looked at this one...
However, I will say that if you like it and haven't already done so, head over to the Pathfinder Database and rate it as you see fit. I definitely appreciate the encouragement, support, and insight/recommendations from everyone on the thread, as the project has progressed much more rapidly thanks to you all!
I do medium to high level atrocities in Excel roughly every week for my own game company. To me, this is kind of a 'ooh, I get to relax' project that I can poke at when I feel like it.
(I've already had one customer ask if I'm the same person as runs Ad Astra Games and does horrifying things with Excel. The answer is 'yes'. *grin*)
You realize that once you've got the data laid out in your two page format that adding a landscape view layout a'la Kor's is a fairly modest investment in time and work...
I personally find that landscape view is much easier to deal with on the gaming table. :)
I do medium to high level atrocities in Excel roughly every week for my own game company. To me, this is kind of a 'ooh, I get to relax' project that I can poke at when I feel like it.
(I've already had one customer ask if I'm the same person as runs Ad Astra Games and does horrifying things with Excel. The answer is 'yes'. *grin*)
You realize that once you've got the data laid out in your two page format that adding a landscape view layout a'la Kor's is a fairly modest investment in time and work...
I personally find that landscape view is much easier to deal with on the gaming table. :)
I'm definitely glad to have you checking things out from a technical perspective. Most of my "real" XLS work is in the IT project management arena and generally doesn't require as much, so this project is somewhat of a training exercise for me (though like you, I do enjoy it as well).
For the landscape view, that is indeed one of the drivers for moving the calculations/named cells over to data tabs, although I'm thinking about it from a laptop use perspective (easier to see the whole sheet). Once I've got that, I can have views for landscape, portrait, low-res monitors, compressed stat blocks, etc. That's a it longer term, as I'd definitely like to get the core functionality nailed and the non-core content at least mostly automated.
I'm going to be going to my local Pathfinder game this Sunday.
If you've got a new build of the sheet ready to run by 10 AM Central on Sunday, I'll re-key my character into it (only 2nd level, so not much re-keying) and give her a whirl.
Else, I'll use the one I sent you by email a few days ago.
I'm going to be going to my local Pathfinder game this Sunday.
If you've got a new build of the sheet ready to run by 10 AM Central on Sunday, I'll re-key my character into it (only 2nd level, so not much re-keying) and give her a whirl.
Else, I'll use the one I sent you by email a few days ago.
Email headed your way with details. For all, I've got a good bit of work done on hammering out the weapon blocks (the ammo for now is down at the bottom and needs "prettying up"). I'll likely strip out the cavalier/oracle data for now so I can go ahead and post a copy once I've shaken it down a bit more.
a great sheet
Hey Erian - I'd love to host your sheet on d20pfsrd.com. Let me know if you'd be interested. Email me or post back here and let me know.
a great sheetHey Erian - I'd love to host your sheet on d20pfsrd.com. Let me know if you'd be interested. Email me or post back here and let me know.
Sure, you've done some great work there and I'd be happy to have a presence. The "official" version is up at the Pathfinder Database right now, so feel free to download from there and stick it on your site as appropriate.
Thanks!
Sure, you've done some great work there and I'd be happy to have a presence. The "official" version is up at the Pathfinder Database right now, so feel free to download from there and stick it on your site as appropriate. Thanks!
Ah, I didn't realize you were already on pathfinderdb. I don't want to have multiple different versions floating around so if you are already set up there I say just stick with one place. I don't need to be stealing any traffic from Tahsin!
Sure, you've done some great work there and I'd be happy to have a presence. The "official" version is up at the Pathfinder Database right now, so feel free to download from there and stick it on your site as appropriate. Thanks!Ah, I didn't realize you were already on pathfinderdb. I don't want to have multiple different versions floating around so if you are already set up there I say just stick with one place. I don't need to be stealing any traffic from Tahsin!
Well, when I say official it's not that it can only be at one place. The folks that make HeroForge also host a copy at their site (as their community was requesting PRPG support). I really would like to have as broad a span of coverage as possible in order to drive up use, and thereby hopefully feedback. Perhaps if you don't want a cross-posting between your site and PFDB you could put a link to his character sheets?
Well, when I say official it's not that it can only be at one place. The folks that make HeroForge also host a copy at their site (as their community was requesting PRPG support). I really would like to have as broad a span of coverage as possible in order to drive up use, and thereby hopefully feedback. Perhaps if you don't want a cross-posting between your site and PFDB you could put a link to his character sheets?
No I understand. What I'm saying though is that I don't want to steal any hits away from pathfinderdb.com. Maybe I'll create another page with links to off-site resources and then redirect people over to him. I didn't want to manage posting updated copies anyway so I was just going to give you access so you could post updates whenever you made them lol
No I understand. What I'm saying though is that I don't want to steal any hits away from pathfinderdb.com. Maybe I'll create another page with links to off-site resources and then redirect people over to him. I didn't want to manage posting updated copies anyway so I was just going to give you access so you could post updates whenever you made them lol
Thanks, that sounds like a great plan. As with my support of 3rd party OGC in my sheet, I also strongly support fan-created sites like yours and the PFDB.
Tower Shields give you a -2 penalty when used in combat due to encumbrance. It is not recognizing this to be the case, neither did build .52 of Pathfinder/Hero Forge.
They both say "You have no penalties because you are proficient with Tower Shields." (My test build was a 1st level Dwarf Fighter with a 16 STR, Weapon Focus and Dazzling Display. Not sure if that has any bearing on it at all.)
If this IS actually the case, PLEASE tell me what page that bit of goodness is on! Cuz I want it! :D
Tower Shields give you a -2 penalty when used in combat due to encumbrance. It is not recognizing this to be the case, neither did build .52 of Pathfinder/Hero Forge.
They both say "You have no penalties because you are proficient with Tower Shields." (My test build was a 1st level Dwarf Fighter with a 16 STR, Weapon Focus and Dazzling Display. Not sure if that has any bearing on it at all.)
If this IS actually the case, PLEASE tell me what page that bit of goodness is on! Cuz I want it! :D
You might want to just use the part of each weapon entry that allows you to manually enter an adjustment to attack on the weapons that you would use with the tower shield. Or you could enter a weapon twice on your sheet, with and without the shield.
It seems like it wouldn't be worthwhile to code something that is so situational.
Tower Shields give you a -2 penalty when used in combat due to encumbrance. It is not recognizing this to be the case, neither did build .52 of Pathfinder/Hero Forge.
They both say "You have no penalties because you are proficient with Tower Shields." (My test build was a 1st level Dwarf Fighter with a 16 STR, Weapon Focus and Dazzling Display. Not sure if that has any bearing on it at all.)
If this IS actually the case, PLEASE tell me what page that bit of goodness is on! Cuz I want it! :D
Note that the released version of the sheet doesn't have automation for armor/weapons and their interactions with feats as yet. That's actually what I'm working on right now for the next release and the impacts from using a tower shield (and also a buckler giving a -1, or a standard shield precluding 2-handed/2-weapon attacks) will indeed be considered for attack rolls. It's not too hard to work in--just a comparison of what is in the Shield field versus weapons wielded. I'm considering adding a "Shield Used" field on the front so you can flip it on and off fast without having to go to the Back tab. You can then dynamically see what effect(s) shields have on weapons.
Speaking of effects, I'll also be building in Conditions so the sheet auto-calcs impacts from things like Fatigue and Nauseated.
I'm considering adding a "Shield Used" field on the front so you can flip it on and off fast without having to go to the Back tab. You can then dynamically see what effect(s) shields have on weapons.
I'd like to see this - any chance it can be in a beta build in the next 23 hours? :)
I'm considering adding a "Shield Used" field on the front so you can flip it on and off fast without having to go to the Back tab. You can then dynamically see what effect(s) shields have on weapons.I'd like to see this - any chance it can be in a beta build in the next 23 hours? :)
It's "family fun and snuggle night" tonight with the family, but if everyone gets to sleep early I might be able to bang something out...
Is there any way to add a custom weapon, and designate it as being eligible for the Weapon Finesse feat?
In the campaign I'm playing in, the DM allows the use of a Cutlass, which is in all respects identical to a Rapier, save that it does slashing damage, instead of piercing.
The other wierdness of our campaign is that although I took a Ranger, the DM allowed a couple of custom substitutions to my Class abilities for starting more as a Sailor. I've been wondering how to edit class abilities, especially ones that grant bonuses to skills, or are 'virtual' feats...
Is there any way to add a custom weapon, and designate it as being eligible for the Weapon Finesse feat?
In the campaign I'm playing in, the DM allows the use of a Cutlass, which is in all respects identical to a Rapier, save that it does slashing damage, instead of piercing.
The other wierdness of our campaign is that although I took a Ranger, the DM allowed a couple of custom substitutions to my Class abilities for starting more as a Sailor. I've been wondering how to edit class abilities, especially ones that grant bonuses to skills, or are 'virtual' feats...
The Data tabs are all editable.. find the one with the weapon list and add your new weapon in. For simplicity's sake, maybe just go ahead and list it as Light
I've used the interim build you sent, and not found any issues beyond me being an idiot about where data gets entered. Looking forward to the next release.
Hi folks. I haven't forgotten about you--just a bit of a break for the holidays with the family. That plus the functionality for two-weapon fighting is driving me nuts...
On the Weapon Finesse issue, I've actually added a Finesse field to the weapon table so you can just note it there. I've also expanded the race table to cover things like movement type and natural armor at a more granular level.
For that minotaur, you're going to have problems with any races that have racial HD. You actually have to use a one-two punch of Custom Class and Custom Race to cover everything. Although not an announced feature, you'll see that I've got the gnoll in place as an example. Select gnoll for race, then add two levels of Gnoll for the class and you've got a 2 HD Humanoid (gnoll) ready for class levels. Well, at least in theory. I haven't tested this bit out very much as yet.
Okay folks, we're at the 11/30 date for releasing the play test classes, so I'm posting the first v.0.7.1 update. This one has jumped head-first into automating combat options like Power Attack and 2-Weapon Fighting, so start testing those out. I've also started automating the non-core feats (gotten down through the "B" names from Paizo). I've also started some internal clean-up and reorganization using some different functions that I think we'll make future modifications easier. I'm working on Flurry of Blows now (it's mostly functional, but only for the first two weapon blocks) but figured I'd go ahead and get this out for everyone to check out while I continue working.
Looking forward to any feedback!
I was looking forward to rekeying my wife's monk with 0.7.1 to check out the Flurry of Blows implementation. However, I get 2 "Too many different cell formats" error popups upon opening the document, followed by "Damage to the file was so extensive that repairs were not possible..." I'm using Excel 2002 SP3. 0.7.0 loads correctly.
Hmm, that error generally indicates too many unique formats are in use. I generally try to stick to some standard styles, but as I've been more focused on expanding functionality than controlling formats lately things may have gotten out of whack. I did a quick pass to remove a few unneeded formats and also ensure everything is using Arial as a font. Re-download (same link, no functional changes) and see if that works.
Thanks!
Re-download (same link, no functional changes) and see if that works.Yup, it loads correctly again. Hopefully I'll still have time to re-key and verify the results before our next game session. Thanks a mil!
Great, and glad to know that fixed the problem. Looks like I'll need to do a good sheet-by-sheet check on the next version to ensure each one is using only the required formats.
excellent.
Will we see the new playtesting base classes in the newer versions?
Yes, definitely. Per requests from Paizo, I won't be releasing the new ones (summoner and witch) until the next batch are out. In the interim, I will be automating the class abilities from the first two to some degree, although I don't want to drop too much effort into that considering how things might shift from the playtest to the final release.