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

Name	D1	D2	D3	D4	D5	D6
Name1	1
Name2	1		1	1
Name3		1
Name4	1				1

<tbody>
</tbody>

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.

D1	D2	D3	D4	D5	D6
I2	J2	K2	L2	M2	N2

<tbody>
</tbody>

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