Add drop down list in cell

[MaxxFusion]

I found instructions on how to do this but I want to take it a step further. I havent been able to find how to do this or not sure what to search for.

In the drop down box after I make a selection I would like that choice to disappear from the drop down. So that way I dont select the same option twice. This would help out greatly when creating the order of my lists.

Is there a way I can do this?

 

[L. Howard]

Try this suggestion.

http://www.contextures.com/xlDataVal03.html

Regards,
Howard

 

[MaxxFusion]

Try this suggestion.

http://www.contextures.com/xlDataVal03.html

Regards,
Howard

I found this same link just after I posted. I have been trying to get this to work since and just cant seem to make it work.

 

[L. Howard]

Hi MaxxFusion,

Here is a workbook that I made.

Works pretty good. May be you can gain something from it.

https://www.dropbox.com/s/mrxxdrcglnec24v/Drop Down with One use Option.xlsm?dl=0

Regards,
Howard

 

[MaxxFusion]

Hi MaxxFusion,

Here is a workbook that I made.

Works pretty good. May be you can gain something from it.

https://www.dropbox.com/s/mrxxdrcglnec24v/Drop Down with One use Option.xlsm?dl=0

Regards,
Howard

Where I may be getting hung up is that I have 11 names instead of 6.

 

[MaxxFusion]

Hi MaxxFusion,

Here is a workbook that I made.

Works pretty good. May be you can gain something from it.

https://www.dropbox.com/s/mrxxdrcglnec24v/Drop Down with One use Option.xlsm?dl=0

Regards,
Howard

Looking at your sheet where is this code supposed to go?
[TABLE="width: 72"]
<colgroup><col width="72"></colgroup><tbody>[TR]
[TD="class: xl63, width: 72"]INDEX(A:A,SMALL(B$1:B$6,1+ROW(A1)-ROW(A$1))))[/TD]
[/TR]
</tbody>[/TABLE]


It seems I have it working now but it doesnt remove the name once I choose it.

 

[MaxxFusion]

Here is my sheet. Maybe you can take a look for me. I am beating my head against the wall as to why this isnt working.

http://goo.gl/2A9T2b

I would like to do that same thing also for each inning for the positions. Every inning i would have positions 1-9 with 2 EH. That would be the same way for each of the 7 innings. It would be possible the each inning would be different.

Then i would like to use this same sheet for a different team that instead of having 11 players it has 12 or 13.

 

[L. Howard]

Looking at your sheet where is this code supposed to go? 


INDEX(A:A,SMALL(B$1:B$6,1+ROW(A1)-ROW(A$1))))

Sloppy of me not to clean the sheet up. That is a fragment of a formula used and should have been deleted. Sorry.



Note that the formulas are not dynamic, therefore you will need to extend the ranges in each to include 11 names instead of 6 in the example.
I extended my example to 20 names and the formulas are changed as seen here in red:

IF(COUNTIF(Schedule!$B$2:$[COLOR="#FF0000"]B$20[/COLOR],A1)>=1,"",ROW())

IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:[COLOR="#FF0000"]B$20[/COLOR]),"",INDEX(A:A,SMALL(B$1:[COLOR="#FF0000"]B$20[/COLOR],1+ROW(A1)-ROW(A$1))))

OFFSET(Employees!$C$1,0,0,COUNTA(Employees!$C$1:$[COLOR="#FF0000"]C$20[/COLOR])-COUNTBLANK(Employees!$C$1:$[COLOR="#FF0000"]C$20[/COLOR]),1)

Howard

 

[MaxxFusion]

Yep i do that but it still doesnt work. All the names are there in the list but it doesnt remove from the list once i choose it. I cant see why.

 

[L. Howard]

On your Team sheet cell B1 change the formula to refer to column D instead of B on the LineUp sheet.

=IF(COUNTIF(LineUp!$D$9:$D$20,A1)>=1,"",ROW())

Seems to work for me now with that change.

Howard