Help with formula for combo box (maybe)

[Jwilts]

I have a list of names in a column.... then I have 6 columns with different disciplines.....

people take part in a certain number of disciplines, I want to be able to make a combo box that will include all people that are participating in one discipline (1 column) and place their last name in the list (can do it by numbers, ie placing a 1 in the column they are participating in and a one comes back not a name).... but I need the name of the person to come back

Does anyone have any idea if this can be done/
thanks, Janet

 

[Panda514]

Hello, I have figured out a way to answer this. I'm assuming you have a list with Names in column A, then Disciplines in columns A to G. I'm not sure if you will ever have duplicate names, but this formula doesn't work if there are duplicate names.

Example Data

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]D1[/TD]
[TD]D2[/TD]
[TD]D3[/TD]
[TD]D4[/TD]
[TD]D5[/TD]
[TD]D6[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Next, make a table to show a list of who is participating in each Discipline and enter the formulas I wrote below. You must press Ctrl+Shift+Enter because the formula is an array. Then you can double click to drop the formula down. You will get a table with a list of names under each discipline. You can see that the only thing that changes in the formula is the column to look at.

[TABLE="width: 500"]
<tbody>[TR]
[TD]D1[/TD]
[TD]D2[/TD]
[TD]D3[/TD]
[TD]D4[/TD]
[TD]D5[/TD]
[TD]D6[/TD]
[/TR]
[TR]
[TD]I2[/TD]
[TD]J2[/TD]
[TD]K2[/TD]
[TD]L2[/TD]
[TD]M2[/TD]
[TD]N2[/TD]
[/TR]
</tbody>[/TABLE]

I2=IFERROR(INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5=1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$2:$A2))),"")
J2=IFERROR(INDEX($A$2:$A$5,SMALL(IF($C$2:$C$5=1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$2:$A2))),"")
K2=IFERROR(INDEX($A$2:$A$5,SMALL(IF($D$2:$D$5=1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$2:$A2))),"")
L2=IFERROR(INDEX($A$2:$A$5,SMALL(IF($E$2:$E$5=1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$2:$A2))),"")
M2=IFERROR(INDEX($A$2:$A$5,SMALL(IF($F$2:$F$5=1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$2:$A2))),"")
N2=IFERROR(INDEX($A$2:$A$5,SMALL(IF($G$2:$G$5=1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$2:$A2))),"")