# Add drop down list in cell

#### MaxxFusion

##### Board Regular
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?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### MaxxFusion

##### Board Regular
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

##### Well-known Member
Code:
``````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:

Code:
``````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

##### Board Regular
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

##### Well-known Member
On your Team sheet cell B1 change the formula to refer to column D instead of B on the LineUp sheet.

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

Seems to work for me now with that change.

Howard

Replies
1
Views
148
Replies
2
Views
111
Replies
0
Views
185
Replies
2
Views
297
Replies
0
Views
218

1,191,691
Messages
5,988,120
Members
440,126
Latest member
duque00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back