Add drop down list in cell

L

L. Howard

Well-known Member

Joined

Oct 16, 2012

Messages

4,514

• Sep 11, 2014

• #2

Try this suggestion.

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

Regards,
Howard

 

Upvote 0

M

MaxxFusion

Board Regular

Joined

Sep 11, 2014

Messages

51

• Sep 11, 2014

• #3

L. Howard said:

Try this suggestion.

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

Regards,
Howard

Click to expand...

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.

 

Upvote 0

L

L. Howard

Well-known Member

Joined

Oct 16, 2012

Messages

4,514

• Sep 11, 2014

• #4

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

 

Upvote 0

M

MaxxFusion

Board Regular

Joined

Sep 11, 2014

Messages

51

• Sep 12, 2014

• #5

L. Howard said:

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

Click to expand...

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

 

Upvote 0

M

MaxxFusion

Board Regular

Joined

Sep 11, 2014

Messages

51

• Sep 12, 2014

• #6

L. Howard said:

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

Click to expand...

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))))

<colgroup><col width="72"></colgroup><tbody>
</tbody>

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

 

Upvote 0

M

MaxxFusion

Board Regular

Joined

Sep 11, 2014

Messages

51

• Sep 12, 2014

• #7

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

L. Howard

Well-known Member

Joined

Oct 16, 2012

Messages

4,514

• Sep 12, 2014

• #8

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

M

MaxxFusion

Board Regular

Joined

Sep 11, 2014

Messages

51

• Sep 12, 2014

• #9

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

L. Howard

Well-known Member

Joined

Oct 16, 2012

Messages

4,514

• Sep 12, 2014

• #10

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