PyroVortex's page

Well, there's definitely some crazy stuff in that other method, and it's arguably a better approach to the last step in my method, but I think what it comes down to is that he and I were addressing somewhat different problems. I was more concerned with filtering out items from lists than setting them to the right length; the last part was just a bit of cleanup to make the list easier to scroll through.

Dynamically defined names... this has potential.
Using both methods, we can do things like:
Feat Type: Combat
Feat: <list of available combat feats in drop-down>

The only difficulty with merging the two techniques is that, unfortunately, I can't make use of the COUNTA function because a formula that evaluates to "" counts as 1 while an empty cell counts as 0, like with the ISBLANK function. Hence the current use of COUNTIF(<blah>,"NULL").

I used your worksheet for a couple of characters I designed recently, and it worked remarkably well for eliminating the tedium that is the core computations of skills, feats, ability scores, etc. (So good work, and thank you for that). On the other hand, there were a few things that bugged me, and since I've done a few crazy things in Excel in the past, I started fiddling with it...

How to eliminate all the annoying blank spaces from filtered data validation drop downs (I was doing this to automate spell lists, but it works for feats, skills, weapons, whatever):

1. Create a column in the relevant table that contains the current row number in the table if some criteria is met (e.g. name is not blank, feat prerequisites are met, etc.), or "" if it is not.

2. Create another column that contains the formula "=SMALL(<entire previous column>,<row number>)". We now have a list of row numbers that contain valid entries in ascending order, followed by a whole ton of cells containing #NUM!

3. Create a final column that, if the previous column evaluates to a number, equals the relevant table entry (i.e. the feat name, spell name, etc.), otherwise "NULL". Now we have a list in the original sorted order, except that all the invalid options are at the end and are "NULL". You will probably want to either insert a blank cell above this column or fiddle with the criteria so that a blank makes it through at the top.

4. Define the final column as List_Feats_Available or whatever it is supposed to be.

Now the crazy part.

5. In the data validation options for the appropriate cell, enter the following (where "<list>" is the name you gave the list):
Type: List
Source: "=OFFSET(<list>,0,0,ROWS(<list>)-COUNTIF(<list>,"NULL"))"

For whatever reason, the OFFSET function gets around the limitation excel imposes on data validation list sources that they cannot refer to cells on other worksheets unless it is a named reference.

When all is said and done, the drop down now contains a blank (assuming the definition included one at the top of the column), followed by all of the valid entries for the drop down. There are neither blanks in the middle, nor trailing blanks.

I've currently applied this to a spell sheet drawing on the Spell DB on d20pfsrd.com, and it works like a charm. I can't vouch for performance issues at the moment, though. I'll post up that part when I get the Spells page to something that actually meshes with it a bit better.