if(isna(match formula

[Anthony]

The following formula will not work and for the life of me I can not
figure out why:
=if(isna(match($a2,$e$2:$m$7,0)),$a2," ")
In cell a1 is the text "All Players". From cells a2-a55, I have the
following data: player1, player2, player3...player54. In cell b1 is
the text "Available players". From cells b2-b55, I have the above
formula so the default values are the same values as column a. From
cells d2-d7 is the text team 1, team 2...team 6. The values in cell
range e2:m7 will be populated by a dropdown box which includes all the
data from cells a2:a55. When I pick a name from the dropdown box in
any cell in the e2:m7 range, the corresponding value in column b
should be removed and replaced by a blank space. At least that is how
I think the above formula should work but it doesn't do anything. Can
someone help me with this? Thanks.

 

[T. Valko]

=if(isna(match($a2,$e$2:$m$7,0)),$a2," ")

The lookup_array has to be a one dimensional array. You're attempting to use
a two dimensional array.

I can't follow the description of your setup so I don't know what you're
trying to do.

 

[Anthony]

Players(a1) Available(b1) Pick 1 Pick
2 Pick 3 Pick 4..........Pick 8
player1(a2) player1(b2) Team1 (e2)
player2 player2 Team2
player3 player3
Team3
player4 player4 Team4
player5 player5 Team5
player6 player6
Team6
(m7)
player7 player7
..........
player54 player54

I have tried to simulate this as best I can above. Just an FYI, I am
trying to create a roster maker for a youth flag football league.
Once I pick a name from the dropdown boxes in cell range e2:m7, i want
the cell holding the equivalent value in the available column to go
blank. If possible, I would like the value in the dropdown box to be
removed as well. This would simply be as a double measure to ensure
the same kid was not placed on two teams. I hope this helps and I
hope you can help me. Thanks.

 

[Anthony]

I got the names to be removed from the dropdown list. I am still
puzzling over the formula in column b.

 

[Anthony]

Thanks for the help. I just realized what you meant by two
dimensional. I rearranged the data and the formula works.

 

[T. Valko]

Ok, I think I understand what you want.

Here's a smaller version of what you have...

A2:A11 = all player names
B2:B11 = available players (this will also be the source for the drop downs
in E2:G8)
D28 = teams names
E2:G8 = data validation drop down lists for player picks

Do this first...

Enter this array formula** in B2 and copy down to B11. This range will be
the source for the drop down lists. As you make selections from the drop
down lists those names will be removed from B2:B11. All available names will
shift upwards so the list will be a contiguous block.

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A$2:A$11,SMALL(IF(COUNTIF(E$2:G$8,A$2:A$11)=0,ROW(A$2:A$11)),ROWS(B$2:B2))-MIN(ROW(A$2:A$11))+1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Setup the drop down lists and use this formula as the source:

=OFFSET($B$2,,,COUNTIF($B$2:$B$11,"?*"))

It might be easier if I put together a small sample file to demonstarte
this. Let me know if you want one.

--
Biff
Microsoft Excel MVP


Players(a1) Available(b1) Pick 1 Pick
2 Pick 3 Pick 4..........Pick 8
player1(a2) player1(b2) Team1 (e2)
player2 player2 Team2
player3 player3
Team3
player4 player4 Team4
player5 player5 Team5
player6 player6
Team6
(m7)
player7 player7
..........
player54 player54

I have tried to simulate this as best I can above. Just an FYI, I am
trying to create a roster maker for a youth flag football league.
Once I pick a name from the dropdown boxes in cell range e2:m7, i want
the cell holding the equivalent value in the available column to go
blank. If possible, I would like the value in the dropdown box to be
removed as well. This would simply be as a double measure to ensure
the same kid was not placed on two teams. I hope this helps and I
hope you can help me. Thanks.

 

[Anthony]

A sample file would be great if you have the time to do one. I would
appreciate if very much.

 

[T. Valko]

Here's a small sample file that demonstrates this.

Use drop down items once.xls 15kb

http://cjoint.com/?dAdWYYLmHH

--
Biff
Microsoft Excel MVP


A sample file would be great if you have the time to do one. I would
appreciate if very much.

 

[T. Valko]

Note that this is based on the data (the player's names) being TEXT.

I can tweak it to cover both text and numbers if need be.