Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
if(isna(match formula
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
if(isna(match formula
=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. -- Biff Microsoft Excel MVP "Anthony" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
if(isna(match formula
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. On Mar 24, 11:33*pm, "T. Valko" wrote: =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. -- Biff Microsoft Excel MVP "Anthony" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
if(isna(match formula
I got the names to be removed from the dropdown list. I am still
puzzling over the formula in column b. On Mar 25, 12:12*am, Anthony wrote: 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. On Mar 24, 11:33*pm, "T. Valko" wrote: =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. -- Biff Microsoft Excel MVP "Anthony" wrote in message .... 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
if(isna(match formula
Thanks for the help. I just realized what you meant by two
dimensional. I rearranged the data and the formula works. On Mar 24, 11:33*pm, "T. Valko" wrote: =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. -- Biff Microsoft Excel MVP "Anthony" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
if(isna(match formula
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) D2:D8 = 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,SMA LL(IF(COUNTIF(E$2:G$8,A$2:A$11)=0,ROW(A$2:A$11)),R OWS(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 "Anthony" wrote in message ... 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. On Mar 24, 11:33 pm, "T. Valko" wrote: =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. -- Biff Microsoft Excel MVP "Anthony" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
if(isna(match formula
A sample file would be great if you have the time to do one. I would
appreciate if very much. On Mar 25, 1:05*am, "T. Valko" wrote: 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) D2:D8 = 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,SMA LL(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 "Anthony" wrote in message ... 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. On Mar 24, 11:33 pm, "T. Valko" wrote: =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. -- Biff Microsoft Excel MVP "Anthony" wrote in message .... 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
if(isna(match formula
Here's a small sample file that demonstrates this.
Use drop down items once.xls 15kb http://cjoint.com/?dAdWYYLmHH -- Biff Microsoft Excel MVP "Anthony" wrote in message ... A sample file would be great if you have the time to do one. I would appreciate if very much. On Mar 25, 1:05 am, "T. Valko" wrote: 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) D2:D8 = 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,SMA LL(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 "Anthony" wrote in message ... 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. On Mar 24, 11:33 pm, "T. Valko" wrote: =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. -- Biff Microsoft Excel MVP "Anthony" wrote in message ... 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
if(isna(match formula
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. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Here's a small sample file that demonstrates this. Use drop down items once.xls 15kb http://cjoint.com/?dAdWYYLmHH -- Biff Microsoft Excel MVP "Anthony" wrote in message ... A sample file would be great if you have the time to do one. I would appreciate if very much. On Mar 25, 1:05 am, "T. Valko" wrote: 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) D2:D8 = 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,SMA LL(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 "Anthony" wrote in message ... 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. On Mar 24, 11:33 pm, "T. Valko" wrote: =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. -- Biff Microsoft Excel MVP "Anthony" wrote in message ... 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match formula question with isna | Excel Worksheet Functions | |||
match with isna formula question | Excel Worksheet Functions | |||
If isna match vlookup formula | Excel Worksheet Functions | |||
IsNA(match | Excel Worksheet Functions | |||
IsNA(match | Excel Worksheet Functions |