Excel 2010 - How to dynamically change drop down list content?

[wbtczn]

This is for a football fantasy draft I have coming in a couple of days. I have a drop down list in every cell for each team owner, by round. The list pulls from a named range in a different worksheet.

What I would like to do is have that range be limited to only include names of players who have not been drafted yet. I have a column next to the player name that says, "UNDRAFTED". I was thinking that could be used to determine if the player's name should be included in the drop down list.

Any suggestions?

 

[T. Valko]

• Each drop down is defined as a list using =Undraftedlist as the source
• Undraftedlist is a named list referring to Players!$AM$2:$AM$968
• Players!$AM$2:$AM$968 is the list where I have this formula:

{=IF(ROWS(AM$2:AM2)>COUNTIF(E$2:E$968,"No"),"",INDEX(B:B,SMALL(IF(E$2:E$968="No",ROW(E$2:E$968)),ROWS(AM$2:AM2))))}

• Column B = the master list of players
• Column E = A Yes/No answer to the question "Drafted?"
• Column AM = the summary list of players remaining that are listed in the drop down list

OK...

Define Undraftedlist as:

=OFFSET(Players!$AM$2,,,COUNTIF(Players!$AM$2:$AM$968,"?*"))

 

[wbtczn]

OK...

Define Undraftedlist as:

=OFFSET(Players!$AM$2,,,COUNTIF(Players!$AM$2:$AM$968,"?*"))

BINGO!!!!!!!!!!

You are AWESOME. That is going to help me so much tonight. THANKS!!!!

 

[T. Valko]

BINGO!!!!!!!!!!

You are AWESOME. That is going to help me so much tonight. THANKS!!!!

Here we go Steelers, here we go!

Good luck. Thanks for the feedback!

 

[wbtczn]

Funny that you say that about the Steelers....the Steeler quarterbacks are my keeper for this year!