|
DropDowns and/or ComboBoxes
I have read the thread intended to answer a question just like this one but
it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
DropDowns and/or ComboBoxes
A data validation drop down will display only 8 items. You can not change
this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
DropDowns and/or ComboBoxes
If the control toolbox is the only way to get all 32 teams into the box I
must use that one. I should say 2 things, The simple drop down would be good enough for me but I am attempting to write this program for my Uncle and I am afraid he will get discouraged if I don't simplify it. Also, probably more importantly the choosen team must be able to be recognized by the vlookup command. Thanks again Dr. Valco -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... "T. Valko" wrote: A data validation drop down will display only 8 items. You can not change this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
DropDowns and/or ComboBoxes
If simplicity is your goal then a data validation list is the way to go.
Why is having to scroll the list such a bad thing? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... If the control toolbox is the only way to get all 32 teams into the box I must use that one. I should say 2 things, The simple drop down would be good enough for me but I am attempting to write this program for my Uncle and I am afraid he will get discouraged if I don't simplify it. Also, probably more importantly the choosen team must be able to be recognized by the vlookup command. Thanks again Dr. Valco -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... "T. Valko" wrote: A data validation drop down will display only 8 items. You can not change this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
New Issue
For whatever reason I am unable to post a new question. While I wait to hear
back from MS I thought I would try to post my newest problem this way with hopes Dr. Valko or someone in the know gets wind of it. OK, Here Goes! I have a 17 sheet workbook (1 for each NFL game week) that contains on each the names of guys/gals from here on known as player(s). Who must, each week, choose 1 NFL team, from that weeks slate of NFL games, that they (the player) thinks will win their perspective contest (NFL game) by the largest €śMargin Of Victory€ť. Sounds simple enough right? What makes it tricky, and the problem I present, is that each NFL team can be used only once for each player in their 17 weeks worth of choices. I have, VLOOKUP for points assessed (negative points should the chosen team loose) and awarded, (how many points their team wins by) ranks and totals for the week and the to date tally, and a dropdown with all 32 NFL teams next to each players name on all 17 sheets. I need to make the dropdown boxes eliminate the chosen NFL team so that the same NFL team is not available for that player in the following weeks. I can make it happen, thanks to Contextures, if each player had his own sheet but that would create a nightmare when mailing out results. Id have to mail, by the end of the NFL season, 17 sheets to 26 different players. Not an option. Can anyone help with this problem or tell me, with absolute certainty; it cant be done on my XP Pro, Excel 2003 Combination. Thanks in Advance, FootBall Express -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: If simplicity is your goal then a data validation list is the way to go. Why is having to scroll the list such a bad thing? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... If the control toolbox is the only way to get all 32 teams into the box I must use that one. I should say 2 things, The simple drop down would be good enough for me but I am attempting to write this program for my Uncle and I am afraid he will get discouraged if I don't simplify it. Also, probably more importantly the choosen team must be able to be recognized by the vlookup command. Thanks again Dr. Valco -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... "T. Valko" wrote: A data validation drop down will display only 8 items. You can not change this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
New Issue
You should be able to do this by setting up the source for the drop downs
that are specific to each "player". It'll be a lot of work but it can be done. What you would do is give the source for each list a defined name that is the specific players name. Then, when you setup the drop down you'd use the players name (which is probably a cell reference) as the source. I think I'd setup a dedicated sheet just for this and then hide this sheet. For example: Sheet1 A1 = Tom B1 = Tom's drop down list of teams Tom's source list of teams is on Sheet2 in the range B1:B32. Give this range the dynamic defined name Tom. Then on Sheet1 the source for Tom's drop down would be =INDIRECT(A1). Each player has their own unique list of teams. If you've seen on the Contextures site how to do it then you should be able to accomplish this although, like i said, it'll be a lot of work. -- Biff Microsoft Excel MVP "Football Express" wrote in message ... For whatever reason I am unable to post a new question. While I wait to hear back from MS I thought I would try to post my newest problem this way with hopes Dr. Valko or someone in the know gets wind of it. OK, Here Goes! I have a 17 sheet workbook (1 for each NFL game week) that contains on each the names of guys/gals from here on known as player(s). Who must, each week, choose 1 NFL team, from that weeks slate of NFL games, that they (the player) thinks' will win their perspective contest (NFL game) by the largest "Margin Of Victory". Sounds simple enough right? What makes it tricky, and the problem I present, is that each NFL team can be used only once for each player in their 17 weeks worth of choices. I have, VLOOKUP for points assessed (negative points should the chosen team loose) and awarded, (how many points their team wins by) ranks and totals for the week and the to date tally, and a dropdown with all 32 NFL teams next to each players name on all 17 sheets. I need to make the dropdown boxes eliminate the chosen NFL team so that the same NFL team is not available for that player in the following weeks. I can make it happen, thanks to Contextures, if each player had his own sheet but that would create a nightmare when mailing out results. I'd have to mail, by the end of the NFL season, 17 sheets to 26 different players. Not an option. Can anyone help with this problem or tell me, with absolute certainty; it can't be done on my XP Pro, Excel 2003 Combination. Thanks in Advance, FootBall Express -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: If simplicity is your goal then a data validation list is the way to go. Why is having to scroll the list such a bad thing? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... If the control toolbox is the only way to get all 32 teams into the box I must use that one. I should say 2 things, The simple drop down would be good enough for me but I am attempting to write this program for my Uncle and I am afraid he will get discouraged if I don't simplify it. Also, probably more importantly the choosen team must be able to be recognized by the vlookup command. Thanks again Dr. Valco -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... "T. Valko" wrote: A data validation drop down will display only 8 items. You can not change this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
New Issue
Biff, Mr. Valko, Sir,
I am getting very close as when I plug the BF list into the 1st and 2nd and 3rd cells I need them in, useing the single sheet code, =IF(COUNTIF(WEEK01!$E$5,A1)=1,"",ROW()) as expected the NFL team chosen in the 1st dropdown does not show up in the second. The same is not true for the third. I believe the problem to be in the expression I used in the B column to make cell E5 in sheets week01 - week17 the desired range. =IF(COUNTIF(WEEK01!$E$5:WEEK17!$E$5,A1)=1,"",ROW( )) Can you tell from this what is wrong. I'm thrilled to be this close and am trying to make it work in one player before going thru all the steps for 26 players X 17 weeks. -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: You should be able to do this by setting up the source for the drop downs that are specific to each "player". It'll be a lot of work but it can be done. What you would do is give the source for each list a defined name that is the specific players name. Then, when you setup the drop down you'd use the players name (which is probably a cell reference) as the source. I think I'd setup a dedicated sheet just for this and then hide this sheet. For example: Sheet1 A1 = Tom B1 = Tom's drop down list of teams Tom's source list of teams is on Sheet2 in the range B1:B32. Give this range the dynamic defined name Tom. Then on Sheet1 the source for Tom's drop down would be =INDIRECT(A1). Each player has their own unique list of teams. If you've seen on the Contextures site how to do it then you should be able to accomplish this although, like i said, it'll be a lot of work. -- Biff Microsoft Excel MVP "Football Express" wrote in message ... For whatever reason I am unable to post a new question. While I wait to hear back from MS I thought I would try to post my newest problem this way with hopes Dr. Valko or someone in the know gets wind of it. OK, Here Goes! I have a 17 sheet workbook (1 for each NFL game week) that contains on each the names of guys/gals from here on known as player(s). Who must, each week, choose 1 NFL team, from that weeks slate of NFL games, that they (the player) thinks' will win their perspective contest (NFL game) by the largest "Margin Of Victory". Sounds simple enough right? What makes it tricky, and the problem I present, is that each NFL team can be used only once for each player in their 17 weeks worth of choices. I have, VLOOKUP for points assessed (negative points should the chosen team loose) and awarded, (how many points their team wins by) ranks and totals for the week and the to date tally, and a dropdown with all 32 NFL teams next to each players name on all 17 sheets. I need to make the dropdown boxes eliminate the chosen NFL team so that the same NFL team is not available for that player in the following weeks. I can make it happen, thanks to Contextures, if each player had his own sheet but that would create a nightmare when mailing out results. I'd have to mail, by the end of the NFL season, 17 sheets to 26 different players. Not an option. Can anyone help with this problem or tell me, with absolute certainty; it can't be done on my XP Pro, Excel 2003 Combination. Thanks in Advance, FootBall Express -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: If simplicity is your goal then a data validation list is the way to go. Why is having to scroll the list such a bad thing? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... If the control toolbox is the only way to get all 32 teams into the box I must use that one. I should say 2 things, The simple drop down would be good enough for me but I am attempting to write this program for my Uncle and I am afraid he will get discouraged if I don't simplify it. Also, probably more importantly the choosen team must be able to be recognized by the vlookup command. Thanks again Dr. Valco -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... "T. Valko" wrote: A data validation drop down will display only 8 items. You can not change this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
New Issue
=IF(COUNTIF(WEEK01!$E$5:WEEK17!$E$5,A1)=1,"",ROW ())
Can you tell from this what is wrong. COUNTIF by itself won't work across multiple sheets. Since your sheet names follow a sequential naming pattern we can use this to come up with a formula that will work. However, to make this formula less complicated (which is a good thing!) I would suggest you change the naming convention and drop the leading 0 from sheets named Week01 thru Week09 and just use Week1, Week2, Week9. Then, the formula becomes: =IF(SUMPRODUCT(COUNTIF(INDIRECT("Week"&ROW(INDIREC T("1:17"))&"!E5"),A1)),"",ROW()) Basically, this formula calculates an array of COUNTIFs. Like this: SUMPRODUCT( COUNTIF(Week1!E5,A1) COUNTIF(Week2!E5,A1) COUNTIF(Week3!E5,A1) COUNTIF(Week4!E5,A1) ) If you use the sheet naming convention of Week01 vs Week1 then it becomes more complicated! -- Biff Microsoft Excel MVP "Football Express" wrote in message ... Biff, Mr. Valko, Sir, I am getting very close as when I plug the BF list into the 1st and 2nd and 3rd cells I need them in, useing the single sheet code, =IF(COUNTIF(WEEK01!$E$5,A1)=1,"",ROW()) as expected the NFL team chosen in the 1st dropdown does not show up in the second. The same is not true for the third. I believe the problem to be in the expression I used in the B column to make cell E5 in sheets week01 - week17 the desired range. =IF(COUNTIF(WEEK01!$E$5:WEEK17!$E$5,A1)=1,"",ROW( )) Can you tell from this what is wrong. I'm thrilled to be this close and am trying to make it work in one player before going thru all the steps for 26 players X 17 weeks. -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: You should be able to do this by setting up the source for the drop downs that are specific to each "player". It'll be a lot of work but it can be done. What you would do is give the source for each list a defined name that is the specific players name. Then, when you setup the drop down you'd use the players name (which is probably a cell reference) as the source. I think I'd setup a dedicated sheet just for this and then hide this sheet. For example: Sheet1 A1 = Tom B1 = Tom's drop down list of teams Tom's source list of teams is on Sheet2 in the range B1:B32. Give this range the dynamic defined name Tom. Then on Sheet1 the source for Tom's drop down would be =INDIRECT(A1). Each player has their own unique list of teams. If you've seen on the Contextures site how to do it then you should be able to accomplish this although, like i said, it'll be a lot of work. -- Biff Microsoft Excel MVP "Football Express" wrote in message ... For whatever reason I am unable to post a new question. While I wait to hear back from MS I thought I would try to post my newest problem this way with hopes Dr. Valko or someone in the know gets wind of it. OK, Here Goes! I have a 17 sheet workbook (1 for each NFL game week) that contains on each the names of guys/gals from here on known as player(s). Who must, each week, choose 1 NFL team, from that weeks slate of NFL games, that they (the player) thinks' will win their perspective contest (NFL game) by the largest "Margin Of Victory". Sounds simple enough right? What makes it tricky, and the problem I present, is that each NFL team can be used only once for each player in their 17 weeks worth of choices. I have, VLOOKUP for points assessed (negative points should the chosen team loose) and awarded, (how many points their team wins by) ranks and totals for the week and the to date tally, and a dropdown with all 32 NFL teams next to each players name on all 17 sheets. I need to make the dropdown boxes eliminate the chosen NFL team so that the same NFL team is not available for that player in the following weeks. I can make it happen, thanks to Contextures, if each player had his own sheet but that would create a nightmare when mailing out results. I'd have to mail, by the end of the NFL season, 17 sheets to 26 different players. Not an option. Can anyone help with this problem or tell me, with absolute certainty; it can't be done on my XP Pro, Excel 2003 Combination. Thanks in Advance, FootBall Express -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: If simplicity is your goal then a data validation list is the way to go. Why is having to scroll the list such a bad thing? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... If the control toolbox is the only way to get all 32 teams into the box I must use that one. I should say 2 things, The simple drop down would be good enough for me but I am attempting to write this program for my Uncle and I am afraid he will get discouraged if I don't simplify it. Also, probably more importantly the choosen team must be able to be recognized by the vlookup command. Thanks again Dr. Valco -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... "T. Valko" wrote: A data validation drop down will display only 8 items. You can not change this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
New Issue
Na, that gives me numbers, 1 thru 32 in the dropdowns, which might be
workable but after choosing the #1 from the DDB, the #1 still shows up in the following weeks. The naming "convention" changed did make things easier to read. -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: =IF(COUNTIF(WEEK01!$E$5:WEEK17!$E$5,A1)=1,"",ROW ()) Can you tell from this what is wrong. COUNTIF by itself won't work across multiple sheets. Since your sheet names follow a sequential naming pattern we can use this to come up with a formula that will work. However, to make this formula less complicated (which is a good thing!) I would suggest you change the naming convention and drop the leading 0 from sheets named Week01 thru Week09 and just use Week1, Week2, Week9. Then, the formula becomes: =IF(SUMPRODUCT(COUNTIF(INDIRECT("Week"&ROW(INDIREC T("1:17"))&"!E5"),A1)),"",ROW()) Basically, this formula calculates an array of COUNTIFs. Like this: SUMPRODUCT( COUNTIF(Week1!E5,A1) COUNTIF(Week2!E5,A1) COUNTIF(Week3!E5,A1) COUNTIF(Week4!E5,A1) ) If you use the sheet naming convention of Week01 vs Week1 then it becomes more complicated! -- Biff Microsoft Excel MVP "Football Express" wrote in message ... Biff, Mr. Valko, Sir, I am getting very close as when I plug the BF list into the 1st and 2nd and 3rd cells I need them in, useing the single sheet code, =IF(COUNTIF(WEEK01!$E$5,A1)=1,"",ROW()) as expected the NFL team chosen in the 1st dropdown does not show up in the second. The same is not true for the third. I believe the problem to be in the expression I used in the B column to make cell E5 in sheets week01 - week17 the desired range. =IF(COUNTIF(WEEK01!$E$5:WEEK17!$E$5,A1)=1,"",ROW( )) Can you tell from this what is wrong. I'm thrilled to be this close and am trying to make it work in one player before going thru all the steps for 26 players X 17 weeks. -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: You should be able to do this by setting up the source for the drop downs that are specific to each "player". It'll be a lot of work but it can be done. What you would do is give the source for each list a defined name that is the specific players name. Then, when you setup the drop down you'd use the players name (which is probably a cell reference) as the source. I think I'd setup a dedicated sheet just for this and then hide this sheet. For example: Sheet1 A1 = Tom B1 = Tom's drop down list of teams Tom's source list of teams is on Sheet2 in the range B1:B32. Give this range the dynamic defined name Tom. Then on Sheet1 the source for Tom's drop down would be =INDIRECT(A1). Each player has their own unique list of teams. If you've seen on the Contextures site how to do it then you should be able to accomplish this although, like i said, it'll be a lot of work. -- Biff Microsoft Excel MVP "Football Express" wrote in message ... For whatever reason I am unable to post a new question. While I wait to hear back from MS I thought I would try to post my newest problem this way with hopes Dr. Valko or someone in the know gets wind of it. OK, Here Goes! I have a 17 sheet workbook (1 for each NFL game week) that contains on each the names of guys/gals from here on known as player(s). Who must, each week, choose 1 NFL team, from that weeks slate of NFL games, that they (the player) thinks' will win their perspective contest (NFL game) by the largest "Margin Of Victory". Sounds simple enough right? What makes it tricky, and the problem I present, is that each NFL team can be used only once for each player in their 17 weeks worth of choices. I have, VLOOKUP for points assessed (negative points should the chosen team loose) and awarded, (how many points their team wins by) ranks and totals for the week and the to date tally, and a dropdown with all 32 NFL teams next to each players name on all 17 sheets. I need to make the dropdown boxes eliminate the chosen NFL team so that the same NFL team is not available for that player in the following weeks. I can make it happen, thanks to Contextures, if each player had his own sheet but that would create a nightmare when mailing out results. I'd have to mail, by the end of the NFL season, 17 sheets to 26 different players. Not an option. Can anyone help with this problem or tell me, with absolute certainty; it can't be done on my XP Pro, Excel 2003 Combination. Thanks in Advance, FootBall Express -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: If simplicity is your goal then a data validation list is the way to go. Why is having to scroll the list such a bad thing? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... If the control toolbox is the only way to get all 32 teams into the box I must use that one. I should say 2 things, The simple drop down would be good enough for me but I am attempting to write this program for my Uncle and I am afraid he will get discouraged if I don't simplify it. Also, probably more importantly the choosen team must be able to be recognized by the vlookup command. Thanks again Dr. Valco -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... "T. Valko" wrote: A data validation drop down will display only 8 items. You can not change this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
New Issue
The season starts tomorrow!
-- Biff Microsoft Excel MVP "Football Express" wrote in message ... Na, that gives me numbers, 1 thru 32 in the dropdowns, which might be workable but after choosing the #1 from the DDB, the #1 still shows up in the following weeks. The naming "convention" changed did make things easier to read. -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: =IF(COUNTIF(WEEK01!$E$5:WEEK17!$E$5,A1)=1,"",ROW ()) Can you tell from this what is wrong. COUNTIF by itself won't work across multiple sheets. Since your sheet names follow a sequential naming pattern we can use this to come up with a formula that will work. However, to make this formula less complicated (which is a good thing!) I would suggest you change the naming convention and drop the leading 0 from sheets named Week01 thru Week09 and just use Week1, Week2, Week9. Then, the formula becomes: =IF(SUMPRODUCT(COUNTIF(INDIRECT("Week"&ROW(INDIREC T("1:17"))&"!E5"),A1)),"",ROW()) Basically, this formula calculates an array of COUNTIFs. Like this: SUMPRODUCT( COUNTIF(Week1!E5,A1) COUNTIF(Week2!E5,A1) COUNTIF(Week3!E5,A1) COUNTIF(Week4!E5,A1) ) If you use the sheet naming convention of Week01 vs Week1 then it becomes more complicated! -- Biff Microsoft Excel MVP "Football Express" wrote in message ... Biff, Mr. Valko, Sir, I am getting very close as when I plug the BF list into the 1st and 2nd and 3rd cells I need them in, useing the single sheet code, =IF(COUNTIF(WEEK01!$E$5,A1)=1,"",ROW()) as expected the NFL team chosen in the 1st dropdown does not show up in the second. The same is not true for the third. I believe the problem to be in the expression I used in the B column to make cell E5 in sheets week01 - week17 the desired range. =IF(COUNTIF(WEEK01!$E$5:WEEK17!$E$5,A1)=1,"",ROW( )) Can you tell from this what is wrong. I'm thrilled to be this close and am trying to make it work in one player before going thru all the steps for 26 players X 17 weeks. -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: You should be able to do this by setting up the source for the drop downs that are specific to each "player". It'll be a lot of work but it can be done. What you would do is give the source for each list a defined name that is the specific players name. Then, when you setup the drop down you'd use the players name (which is probably a cell reference) as the source. I think I'd setup a dedicated sheet just for this and then hide this sheet. For example: Sheet1 A1 = Tom B1 = Tom's drop down list of teams Tom's source list of teams is on Sheet2 in the range B1:B32. Give this range the dynamic defined name Tom. Then on Sheet1 the source for Tom's drop down would be =INDIRECT(A1). Each player has their own unique list of teams. If you've seen on the Contextures site how to do it then you should be able to accomplish this although, like i said, it'll be a lot of work. -- Biff Microsoft Excel MVP "Football Express" wrote in message ... For whatever reason I am unable to post a new question. While I wait to hear back from MS I thought I would try to post my newest problem this way with hopes Dr. Valko or someone in the know gets wind of it. OK, Here Goes! I have a 17 sheet workbook (1 for each NFL game week) that contains on each the names of guys/gals from here on known as player(s). Who must, each week, choose 1 NFL team, from that weeks slate of NFL games, that they (the player) thinks' will win their perspective contest (NFL game) by the largest "Margin Of Victory". Sounds simple enough right? What makes it tricky, and the problem I present, is that each NFL team can be used only once for each player in their 17 weeks worth of choices. I have, VLOOKUP for points assessed (negative points should the chosen team loose) and awarded, (how many points their team wins by) ranks and totals for the week and the to date tally, and a dropdown with all 32 NFL teams next to each players name on all 17 sheets. I need to make the dropdown boxes eliminate the chosen NFL team so that the same NFL team is not available for that player in the following weeks. I can make it happen, thanks to Contextures, if each player had his own sheet but that would create a nightmare when mailing out results. I'd have to mail, by the end of the NFL season, 17 sheets to 26 different players. Not an option. Can anyone help with this problem or tell me, with absolute certainty; it can't be done on my XP Pro, Excel 2003 Combination. Thanks in Advance, FootBall Express -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: If simplicity is your goal then a data validation list is the way to go. Why is having to scroll the list such a bad thing? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... If the control toolbox is the only way to get all 32 teams into the box I must use that one. I should say 2 things, The simple drop down would be good enough for me but I am attempting to write this program for my Uncle and I am afraid he will get discouraged if I don't simplify it. Also, probably more importantly the choosen team must be able to be recognized by the vlookup command. Thanks again Dr. Valco -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... "T. Valko" wrote: A data validation drop down will display only 8 items. You can not change this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur failed. If the only solution to my issue is a combo box could you briefly explain how to make one that contains all 32 teams. I would much rather the DropDown approach. Thanks in Advance James -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... |
New Issue
No the season started Thursday the 4th.
-- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: The season starts tomorrow! -- Biff Microsoft Excel MVP "Football Express" wrote in message ... Na, that gives me numbers, 1 thru 32 in the dropdowns, which might be workable but after choosing the #1 from the DDB, the #1 still shows up in the following weeks. The naming "convention" changed did make things easier to read. -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: =IF(COUNTIF(WEEK01!$E$5:WEEK17!$E$5,A1)=1,"",ROW ()) Can you tell from this what is wrong. COUNTIF by itself won't work across multiple sheets. Since your sheet names follow a sequential naming pattern we can use this to come up with a formula that will work. However, to make this formula less complicated (which is a good thing!) I would suggest you change the naming convention and drop the leading 0 from sheets named Week01 thru Week09 and just use Week1, Week2, Week9. Then, the formula becomes: =IF(SUMPRODUCT(COUNTIF(INDIRECT("Week"&ROW(INDIREC T("1:17"))&"!E5"),A1)),"",ROW()) Basically, this formula calculates an array of COUNTIFs. Like this: SUMPRODUCT( COUNTIF(Week1!E5,A1) COUNTIF(Week2!E5,A1) COUNTIF(Week3!E5,A1) COUNTIF(Week4!E5,A1) ) If you use the sheet naming convention of Week01 vs Week1 then it becomes more complicated! -- Biff Microsoft Excel MVP "Football Express" wrote in message ... Biff, Mr. Valko, Sir, I am getting very close as when I plug the BF list into the 1st and 2nd and 3rd cells I need them in, useing the single sheet code, =IF(COUNTIF(WEEK01!$E$5,A1)=1,"",ROW()) as expected the NFL team chosen in the 1st dropdown does not show up in the second. The same is not true for the third. I believe the problem to be in the expression I used in the B column to make cell E5 in sheets week01 - week17 the desired range. =IF(COUNTIF(WEEK01!$E$5:WEEK17!$E$5,A1)=1,"",ROW( )) Can you tell from this what is wrong. I'm thrilled to be this close and am trying to make it work in one player before going thru all the steps for 26 players X 17 weeks. -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: You should be able to do this by setting up the source for the drop downs that are specific to each "player". It'll be a lot of work but it can be done. What you would do is give the source for each list a defined name that is the specific players name. Then, when you setup the drop down you'd use the players name (which is probably a cell reference) as the source. I think I'd setup a dedicated sheet just for this and then hide this sheet. For example: Sheet1 A1 = Tom B1 = Tom's drop down list of teams Tom's source list of teams is on Sheet2 in the range B1:B32. Give this range the dynamic defined name Tom. Then on Sheet1 the source for Tom's drop down would be =INDIRECT(A1). Each player has their own unique list of teams. If you've seen on the Contextures site how to do it then you should be able to accomplish this although, like i said, it'll be a lot of work. -- Biff Microsoft Excel MVP "Football Express" wrote in message ... For whatever reason I am unable to post a new question. While I wait to hear back from MS I thought I would try to post my newest problem this way with hopes Dr. Valko or someone in the know gets wind of it. OK, Here Goes! I have a 17 sheet workbook (1 for each NFL game week) that contains on each the names of guys/gals from here on known as player(s). Who must, each week, choose 1 NFL team, from that weeks slate of NFL games, that they (the player) thinks' will win their perspective contest (NFL game) by the largest "Margin Of Victory". Sounds simple enough right? What makes it tricky, and the problem I present, is that each NFL team can be used only once for each player in their 17 weeks worth of choices. I have, VLOOKUP for points assessed (negative points should the chosen team loose) and awarded, (how many points their team wins by) ranks and totals for the week and the to date tally, and a dropdown with all 32 NFL teams next to each players name on all 17 sheets. I need to make the dropdown boxes eliminate the chosen NFL team so that the same NFL team is not available for that player in the following weeks. I can make it happen, thanks to Contextures, if each player had his own sheet but that would create a nightmare when mailing out results. I'd have to mail, by the end of the NFL season, 17 sheets to 26 different players. Not an option. Can anyone help with this problem or tell me, with absolute certainty; it can't be done on my XP Pro, Excel 2003 Combination. Thanks in Advance, FootBall Express -- To those who have helped in the past and those about to help, Thank You. There is an intregeing element to Excel that has captured me and that stimulates my every remaining brain cell. Awesome "T. Valko" wrote: If simplicity is your goal then a data validation list is the way to go. Why is having to scroll the list such a bad thing? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... If the control toolbox is the only way to get all 32 teams into the box I must use that one. I should say 2 things, The simple drop down would be good enough for me but I am attempting to write this program for my Uncle and I am afraid he will get discouraged if I don't simplify it. Also, probably more importantly the choosen team must be able to be recognized by the vlookup command. Thanks again Dr. Valco -- To those who have helped in the past, and those who have tried to help, Thank - You... To those about to rock, I salute you... "T. Valko" wrote: A data validation drop down will display only 8 items. You can not change this. You can use a combo box which let's you set the number of items displayed but there are 2 types of combo boxes and which is best to use depends. There is a Forms cb and a Control Toolbox cb. I use a 17" monitior and the Forms cb with 32 items won't fit on my screen (at 100% zoom with the cb on row 1) and the list gets truncated. This type of cb is easier to setup. The Control Toolbox cb does fit all 32 items but this type of cb is slightly more complicated to setup. So, which one do you think you need? -- Biff Microsoft Excel MVP "Football Express" wrote in message ... I have read the thread intended to answer a question just like this one but it increased my confusion. Useing Excel 2003 I need a dropdown box that contains all 32 professional football teams. That part is easy enough and is done. BUT I need the same said dropdown to be long enough to show all 32 teams without useing the side scroll. Is it Possible in EXCEL 2003? I attempted useing the combo box described in the above mentioned thread bur |
| All times are GMT +1. The time now is 08:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com