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]

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?

Here's a recent thread on the subject. There's even a sample file.

http://www.mrexcel.com/forum/showthread.php?t=574996

 

[wbtczn]

Thanks Biff. That example sure looks like what I'm trying to do. I've created a new column. The first cell loaded the first player's name just fine. When I went to my other page and drafted the player, that player's name should have left the list, but it didn't.

A second issue I ran into was that when I dragged the cell down to fill in all of the other rows within that column, I got a #NUM! in all except the last 15 or so.

I've compared the formulas from the 2 spreadsheets, and they seem to be the same (outside of column differences)

I've got a screen print of that sheet, but can't figure out how to attach it here. However, here are the 2 formuals I have been comparing:

Your spreadsheet:
=IF(ROWS(C$2:C2)>COUNTIF(B$2:B$17,"N"),"",INDEX(A:A,SMALL(IF(B$2:B$17="N",ROW(B$2:B$17)),ROWS(C$2:C2))))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o>My spreadsheet:</o>
=IF(ROWS(E$3:E3)>COUNTIF(F$3:F$1000,"No"),"",INDEX(B:B,SMALL(IF(F$3:F$1000="No",ROW(F$3:F$1000)),ROWS(E$3:E3))))<o></o>

 

[T. Valko]

Thanks Biff. That example sure looks like what I'm trying to do. I've created a new column. The first cell loaded the first player's name just fine. When I went to my other page and drafted the player, that player's name should have left the list, but it didn't.

A second issue I ran into was that when I dragged the cell down to fill in all of the other rows within that column, I got a #NUM! in all except the last 15 or so.

I've compared the formulas from the 2 spreadsheets, and they seem to be the same (outside of column differences)

I've got a screen print of that sheet, but can't figure out how to attach it here. However, here are the 2 formuals I have been comparing:

Your spreadsheet:
=IF(ROWS(C$2:C2)>COUNTIF(B$2:B$17,"N"),"",INDEX(A:A,SMALL(IF(B$2:B$17="N",ROW(B$2:B$17)),ROWS(C$2:C2))))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o>My spreadsheet:</o>
=IF(ROWS(E$3:E3)>COUNTIF(F$3:F$1000,"No"),"",INDEX(B:B,SMALL(IF(F$3:F$1000="No",ROW(F$3:F$1000)),ROWS(E$3:E3))))<o></o>

Are all the drop down lists in a contiguous range?

 

[wbtczn]

No, the drop downs are in 10 contiguous ranges -- one for each team...and they would all pull from the same source list.

 

[T. Valko]

No, the drop downs are in 10 contiguous ranges -- one for each team...and they would all pull from the same source list.

OK.

Well, I'm getting ready to call it a day.

I'll put something together tomorrow when I get a chance.

 

[wbtczn]

Thanks. Sorry I took so long to get back to you. Busy day today.

 

[wbtczn]

I figured it out. There was one difference between the 2 spreadsheets. Yours had { } around the equation. I brushed it off as a difference in Excel versions. I did some digging and figured out that I needed to hit control+shift+enter when I was done editing the cell.

Here's where I found that info: http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=103

I'm up and going. This is a huge improvement for me. Now I've got one more question -- is there a way to get the drop down boxes to ignore the blank cells?

Once again -- thanks!!!

 

[T. Valko]

I figured it out. There was one difference between the 2 spreadsheets. Yours had { } around the equation. I brushed it off as a difference in Excel versions. I did some digging and figured out that I needed to hit control+shift+enter when I was done editing the cell.

Here's where I found that info: http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=103

I'm up and going. This is a huge improvement for me. Now I've got one more question -- is there a way to get the drop down boxes to ignore the blank cells?

Once again -- thanks!!!

Good deal on getting that figured out!

What is the range address that you're using as the source of the drop downs?

And, post the formula that you are currently using to define the source.

 

[wbtczn]

• 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