Drop down list from 3 or 2 or 1 or 0 number selection

**Joah** · 06-20-2012, 12:48 AM

I have three categories,

Strike
Expertise
Capability

If a cell has a value of "Human", it will will have an associated strength value of 3,2,1, or 0

How could I create a list for each of the three categories that would allow the values to be selected (either 3,2,1, or 0)?

Would it be possible to subtract the value once selected (eg 2 of 3) and have only the remainder value possible for the subsequent lists? In the example either 1 or 1 for the remaining two categories.

Thanks,

**npamcpp** · 06-20-2012, 04:57 AM

Not sure I completely understand what you want to achieve, but have a look at the attached file. In the three shaded cells, use the dropdown to select from a list of values. As soon as you have selected a value, it will no longer show in the other drop downs. The formulas to create the drop down list are in the same sheet.

**Joah** · 06-20-2012, 10:22 AM

This is very good, thank you for your help.

The whole idea of assigning points from an available pool is to strengthen the unit. So we have 3 points available from making the unit all humans. The player can then assign either 3 points to one of the categories, or 1 point to each of the categories or 2 and 1 and 0 respective.

I see that the formula offered will have a drop down menu based upon a predetermined list of 0 1 2 3, but what if there are only 2 points available? For example if the unit of humans has a creature attached it only gets 1 bonus point. This latter part has already been figured out in my sheet, thanks again to the community.

Also, in the attached file if I select the number 2 from the pull down list on my first category, it will have 0 1 3 on the list of my next category and that should be 0 1.

I notice that the list does not leave a formula footprint in a cell, and when I tried to find list in formula its not there. hmm,

Cheers,

**Joah** · 06-20-2012, 11:49 AM

I guess there would have to be four conditions available to the list.

If there are 3 points then list 3,2,1
If there are 2 points then list 2,1
If there is 1 point then list 1
If there are no points then list nothing.

Part of the problem I am encountering is with the cell that has the data validation list, if I try to enter a function then excel will give me an error about some dude locking the cell, blah blah blah

So, do I assign the conditions within the data validation rules? Or do I make four or more separate cells with vlookup for each?

hmm,