Character Sensitive Selectable Lists
Sometimes a character should not be able to select an option from your list.
For example, if you do not have any SURGE qualities, you should not be able to
select Meta genetic qualities. Or if you are not Magically active, you should not
be able to select the 'Astral Chameleon' quality.
This is best explained by looking at the SR4CG_DK sheet.
If you dont have a copy, check my sig for a link.
Open the sheet and unhide the Qualities_Data sheet. (in excel 2003 select 'Format->Sheet->Unhide')
The root of the 'Selectable Qualities' table is a list of all selectable qualities (column E).
The table has three columns of lookup data, BP cost (F), Book (G) and Page (H).
The next Five columns are flags used to define when the quality is selectable.
For example, see 'Adrenaline Surge' at the top. The Metagenetic column (I) is a 'N',
as is the Infected Column (J). These indicate that the Quality is not
in the Metagenetic or Infected group of qualities.
Column K is 'Y' indicating the quality is Normal. This means it can be selected as a default.
The next two columns specify if the quality is selectable by AI and Free Spirit Characters.
Next, column (n) is a flag indicating P for 'P'ositive quality or 'N' for 'N'egative quality.
The last column is a general 'Disable' column. For most qualities it is blank. For qualities
with special requirements, it contains a test that results in FALSE if the character
does not match the requirements.
See 'Astral Chameleon' (row 22). Cell O22 has the simple test: =NOT(Is_Magic)
This could say, =IF(Is_Magic,FALSE,TRUE) or =IF(Is_Magic=TRUE, TRUE, FALSE).
But a NOT statement returns a TRUE/FALSE, so it was simpler.
Now look at column D. This column examines all of the other columns and determines the Selectable state of the quality.
First, if E17 = "" (two double quotes indicates a NULL or Nothing) then D will be FALSE.
Otherwise, it will test if O17 is TRUE. If O17 (the Disable Column) is TRUE, then Selectable will be 'FALSE'.
Next it goes through and checks each column in the table to see if the character meets the requirements. The final result will be 'TRUE' if the quality is selectable by the character, and FALSE if it is not.
Now we have a TRUE and FALSE column, but we still have a full list of every quality. We could move the 'Selectable' column to the right end of the table, then simply let the user pick any quality they want, and then do a VLOOKUP on the quality to return the TRUE/FALSE selectable status of what they chose, then if they chose something they shouldnt we could use Conditional Formatting to Highlight the column red.
We do want to Highlight errors, but even better we want to prevent the user from even seeing the INVALID options in their drop down list.
Another option is to edit the Qualities Column (E) so that each entry basically said: =IF(D22=TRUE,"Astral Chameleon","")
This solution would prevent invalid options from appearing in the dropdown, but in Excel2003 this left us with actual BLANK spaces in the drop down list. UUUUUGGGGLY!
The workaround I discovered was to create another List, which was a list of actually Selectable Qualities, then make that list a lookup against the original list.
But to get the list to lose the spaces I had to only select options where the Quality was selectable.
So I added Column B and C. Column C is for a full list of all Selectable Qualities, and Column B is for a list of only selectable Negative Qualities. The formula in C basically numbers the rows when they are selectable, and leaves a 0 if they are not.
See Cell C3: =IF($D18,MAX(C$16:C17,0)+1,0)
If D18 is true, then the quality is selectable. If it is selectable, we want to increment the count, otherwise put a 0.
To increment the count, we look at the MAXimum value from the numbers in the list above. C16 has a 1 and C17 has a 2, so the max between C16 and C17 is 2. We add 1 to that and the result is 3.
You can see where this really is important when you get to row 23: Astral Chameleon is not selectable (assuming you have not added 'Adept' or 'Magician' etc. to the Main_Sheet's 'Special' selection), so the number in column C is 0.
The next line 'Astral Sight' picks up at 7, where column 21 left off.
Now lets look at the selectable qualities list. See Column Q and R.
Column Q is a simple number list, from 1 to the maximum qualities we could possibly see in the list.
Column R has a formula that performs a lookup against the numbered portion of the selectable qualities table.
First, it checks to make sure that Q3 does not exceed the count of how many qualities are currently selectable (see C15, where it gets the maximum from column C).
If Q3 (1) does not exceed the maximum (should be around 284), then it will perform a lookup.
Now if you do a lookup on a table and there are no results found, you may end up with a 0 in the column. To prevent the character sheet from having any random 0's in it, most of my lookups are actually a double lookup.
First it performs the lookup and checks for an empty or 0 value. If the result is empty, then I assign "" (Null).
If there is actually a result to see, then I do the same lookup again to populate the value in the cell.
So the lookup checks Q3 (1) against the qualities Index Table (Index version of Qualities table starts with the Index number in column C, instead of at the qualities list in Column E).
If should find the '1' in cell C16 and return the value from E16, which is '<< Positive Qualities >>'. That is actually a header that helps segregate the list of qualities.
Looking at Q4 we see that it found index 2, which resolves to 'Adrenaline Surge'.
Looking down Column R you should see a nice final list of selectable qualities, without uneeded spaces in it.
If you are using a program other than excel 2003, the program may automatically filter out blanks, but you should use this method so that your sheet is easily usable by all spreadsheet programs.
And that is how to make a selectable list. For another example, see selectable Metamagics. 'Magic_Data tab, columns AL through AS'.