New Issue

[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.