Add drop down list in cell

MaxxFusion

Board Regular
Joined
Sep 11, 2014
Messages
51
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
Joined
Sep 11, 2014
Messages
51
Upvote 0

MaxxFusion

Board Regular
Joined
Sep 11, 2014
Messages
51
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.
 
Upvote 0

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 
Upvote 0

MaxxFusion

Board Regular
Joined
Sep 11, 2014
Messages
51
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.
 
Upvote 0

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top