Removing Blank Cells From With In A Drop Down List

[fabio73]

I have seen a few other forum posts with regards to this question however, I have found the answers provided lacking sufficient explanation for myself to understand how to apply the solutions given to my particular situation.

I am currently working on a character equipment assistant spreadsheet for an NES Rom Hack called Final Fantasy Hard Type. It is a hack, developed by Zzonkmiles, to the NES Classic, Final Fantasy, that increases the difficulty dramatically, as well as fixing a lot of bugs that were present in the original game. The goal of the spreadsheet is to help players see in advance how certain weapons and armor combinations will improve their selected characters stats, damage output, defense against attacks, resistances to spells, and additional spell buffs.

There are a total of 12 individual characters that can be played in the game, each with their own set of weapons, armor, and magic skills. If you look at columns U - AG on the 'Weapons' tab in the spreadsheet I have provided, you will see a list of the 12 characters at the top, along with the corresponding weapons that that particular character can equip throughout the game. The idea is set up the 'STATS' tab so that based on the character you pick in the drop down list in cell B11, cells G12, L12, L18, L24, and L30, will auto populate with that particular characters equipable weapons and armor.

To accomplish this task, the plan was to create multiple 'Defined Name' definitions for each characters weapons and armor, and then have the data validated drop down lists in corresponding cells in G12, L12, L18, L24, and L30 on the STATS tab, populate that characters lists using the CHOOSE function based on a character index number that would appear in cell C11. However, because the lists I would create have spaces in between values, when those lists are populated into the drop down list, the spaces are populated too and it looks ugly. Also, because of the spaces in between values, making the lists dynamic is also difficult, as using =OFFSET(A1,0,0,COUNTA(A:A)-1,) method doesn't take into account the spaces in between values and there for will not capture all the data in the list, even though it counts how many are in the list properly.

The idea is making updates to the spreadsheet easier for future versions of the game where new weapons and armor may be introduced or removed or certain characters gain the ability to use different weapons and armor. So what would be the best way to make the lists dynamic and get rid of those blanks within the drop down box?

I know it's a long explanation of what I need help with but I figured more info was better then less. THANK YOU in advance for helping me to resolve my problem!

[tigeravatar]

fabio73,

Attached is a modified version of your uploaded workbook. In the 'Weapons' tab, I deleted column U to remove the duplicate column Weapon List. Now the class names start in 'Weapons'U1 beginning with FIGHTER

In cell U2 and copied over and down is this array formula. Array formulas must be confirmed with Ctrl+Shift+Enter and not just Enter. That will tell excel it is an array formula and excel will automatically add the curly braces {} around the formula:

Please Login or Register  to view this content.

I then created a named range called CharacterSelection which is:

Please Login or Register  to view this content.

Lastly I made a named range called WeaponList which is:

Please Login or Register  to view this content.

In the 'STATS' worksheet, cell G12, is the data validation drop-down list set to:

Please Login or Register  to view this content.

That way, whenever the Character is changed in sheet 'STATS', cell B11, the weaponlist is updated for the appropriate character without blanks. Is something like that what you're looking for?

[fabio73]

This is exactly what I was looking for! If it is not too much trouble, could you explain to me in detail how the code written for the array formula and the named range, WeaponsList, work, so that I can practice applying the formulas to the Armor, Shields, Helmets, and Gauntlets tabs?

Thank you for the assistance, tigeravatar!!!

Moderator's Note: As per Forum Rule #12, please don't quote whole posts unless necessary…it's just clutter...Thanks.

[tigeravatar]

fabio73,

The array formula:

Please Login or Register  to view this content.

IF(COUNTA(U$1:U1)>COUNTIF(H$2:H$48,"X"),"" -
This looks at the number of populated cells from U1 (row anchored) to U1 (the cell one above U2. In the U3 formula, this will be U1:U2, etc). If the number of populated cells so far in column U is greater than the number of X's in column H then return blank (note that only the rows are anchored, the column is not, for both column H and column U). column H is the list of weapons marked for FIGHTER with an X. Column U is the formula output for the list of weapons for FIGHTER.

,INDEX($G$1:$G$48 -
So the formula found that the number of populated cells in column U is not greater than the number of X's in column H. So instead of returning blank, we're going to return one of the items from column G (note that both column and row are anchored here. Column G is the list of weapon names).

,SMALL(IF(H$2:H$48="X",ROW(H$2:H$48)),COUNTA(U$1:U1)))) -
Of the items we've loaded into the Index, we want to return the appropriate item for this cell. So first we find all the X's in column H and get their row numbers and load that into an array (which is why this is an array formula). Then we determine which one we need using the SMALL bit. The first result we need would have the lowest row number, and the second result we need would have the second-lowest row number, and so on. The COUNTA is the portion telling which smallest row number to return. For cell U2, it will be COUNTA(U$1:U1) which = 1 so get the first-smallest row number. For cell U3, it will be COUNTA(U$1:U2) which = 2 so get the second-smallest row number, and so on.


That's how the array formula works. When you put it all together, it roughly says this:
"Have I returned all items marked with an X for this character?
If Yes -> output blank
If No -> load the row numbers of all items containing X's into an array, Then output the next smallest row number matching column G"



The named range, WeaponsList:

Please Login or Register  to view this content.

This uses Offset and Match to get the correct location for the character chosen from the CharacterSelection drop-down. It looks long, but its really quite simple:

If CharacterSelection="" (if that cell is blank), then return T1 (which is a blank cell. So if a character isn't chosen, there are no available weapon choices).

Otherwise start at T1 and go down 1 cell, and to the right a number of cells until a match is found from CharacterSelection (this will make it start at row 2 of the appropriate character).

Then resize it so that instead of just 1 cell, it returns a number of cells = the total number of rows - the number blanks - 1 (the last -1 is to correct for the header). Finding the number of blanks is just another offset/match to get to the correct column.