DropDowns and/or ComboBoxes

[Football Express]

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

 

[T. Valko]

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?

 

[Football Express]

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]

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?

 

[Football Express]

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

 

[T. Valko]

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.

 

[Football Express]

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.

 

[T. Valko]

=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(INDIRECT("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!

 

[Football Express]

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.

 

[T. Valko]

The season starts tomorrow!

 

[Football Express]

No the season started Thursday the 4th.