Generate list from Dropdown Bar

[holt0222]

I am trying to create a drop down menu on the MOR Calculation Sheet from the Officer List Sheet. I have already created the dropdown menu now I just need to figure out how to make it so that when I select the person from the drop down, it generates the list for everyone under them into the MOR Calcuation.

Officer List

MOR Calculator 2022 Help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Ward Ralph	Cook Tim	Craigs Don	Thomas Randal	Remington Jamison	Corbin Rowan	Jacqueline Hardy	Benjamin Gutierrez	Al Powell	Verna Hansen	Alejandro Ball	Cary Peterson	Erika Hubbard	Adrian Briggs	Carolyn Gilbert	Erica Luna	Billie Pope
2	Debbie Buchanan	Carmen Owen	Jacquelyn Phelps	Betsy Ball	Theresa Edwards	Melody Rivera	Tyrone Chambers	Ricky Briggs	Carrie Pratt	Armando Flores	Brenda Park	Guadalupe Ballard	Latoya Hopkins	Roberta Clarke	Kenneth May	Shirley Welch	Sonya Caldwell
3	Gene Erickson	Willard Boyd	Mercedes Steele	Cristina Terry	Wendell Butler	Mario Collins	Preston Burns	Kevin Mcbride	Willie Matthews	Allison Schmidt	Paul Padilla	Gail Sutton	Doyle Buchanan	Douglas Gardner	Clarence Coleman	Nadine Gross	Lora Gomez
4	Jeffery Todd	Dixie George	Muriel Jimenez	Estelle Yates	Margie Blair	Eleanor Weaver	Lori Fisher	Alfred Butler	Nettie Walton	Curtis Bell	Agnes Norton	Garrett Guzman	Lorraine Richardson	Marvin Hill	Curtis Patrick	Max Norris	Daisy Powell
5	Frederick Coleman	Ken Oliver	Henrietta Woods	Grady Johnston	Marlon Lucas	Alicia Thompson	Bessie Johnston	Shelley Grant	Javier Becker	Kari King	Nathaniel Terry	Paul King	Carolyn Olson	Frank Collier	Blake Carter	Cory Ortega	Walter Gutierrez
6	Erica Douglas	Ernestine Sandoval	Irma Pratt	Bethany Hughes	Lewis Coleman	Cecelia Abbott	Patti Pittman	Johanna Hart	Olivia Malone	Dustin Moreno	Franklin Grant	Maggie Christensen	Camille Kim	Jacob Houston		Ervin Stanley
7	Marjorie Montgomery	Joanna Harris	Lori Leonard	Garry Coleman	Eloise Henry	Antoinette Zimmerman	Kimberly Mcguire	Sally Frazier	Brooke Burton	Harvey Holmes	Joel Gross			Miriam Gonzales
8	Julian Greene				Flora Bradley	Paul Newman	Peter Lucas	Dixie Stephens	Nina Benson		Matthew Jefferson
9	Margarita Lowe						Jeremy Johnson	Kendra Bryan
Officer List

MOR Calculation

MOR Calculator 2022 Help.xlsx
	A	B	C
1	Ward Ralph	MOR	Pass FET
2
3
4
5
6
7			1
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23			1
24
25
26
27
28
29
30
31
32
33
34
35
MOR Calculation

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=Supervisors

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How is 'Supervisors' defined?

 

[holt0222]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How is 'Supervisors' defined?

Supervisors is simply A1:Q1 in the Officer List

 

[Peter_SSs]

Thanks for updating your details.

Check the table name of the table in Officer List (Table1 in my workbook) to see if this does what you want.

holt0222.xlsm
	A
1	Ward Ralph
2	Debbie Buchanan
3	Gene Erickson
4	Jeffery Todd
5	Frederick Coleman
6	Erica Douglas
7	Marjorie Montgomery
8	Julian Greene
9	Margarita Lowe
10
MOR Calculation

Cell Formulas
Range	Formula
A2:A9	A2	=LET(f,FILTER(Table1,Table1[#Headers]=A1),FILTER(f,f<>""))
Dynamic array formulas.

 

[holt0222]

Thanks for updating your details.

Check the table name of the table in Officer List (Table1 in my workbook) to see if this does what you want.

holt0222.xlsm
	A
1	Ward Ralph
2	Debbie Buchanan
3	Gene Erickson
4	Jeffery Todd
5	Frederick Coleman
6	Erica Douglas
7	Marjorie Montgomery
8	Julian Greene
9	Margarita Lowe
10
MOR Calculation

Cell Formulas
Range	Formula
A2:A9	A2	=LET(f,FILTER(Table1,Table1[#Headers]=A1),FILTER(f,f<>""))
Dynamic array formulas.

Thank you very much that has solved the issue that I was running into.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[holt0222]

You're welcome. Thanks for the follow-up.

So I have run into an issue with the formula. My work uses an older version, 2016, and it does not support the Filter function. Any idea on the best way to correct this.

 

[Peter_SSs]

Try this instead.

holt0222.xlsm
	A	B
1	Verna Hansen	10
2	Armando Flores
3	Allison Schmidt
4	Curtis Bell
5	Kari King
6	Dustin Moreno
7	Harvey Holmes
8
9
10
MOR Calculation

Cell Formulas
Range	Formula
B1	B1	=MATCH(A1,Table1[#Headers],0)
A2:A10	A2	=IFERROR(INDEX(Table1,ROWS(A$2:A2),B$1)&"","")

 

[holt0222]

Try this instead.

holt0222.xlsm
	A	B
1	Verna Hansen	10
2	Armando Flores
3	Allison Schmidt
4	Curtis Bell
5	Kari King
6	Dustin Moreno
7	Harvey Holmes
8
9
10
MOR Calculation

Cell Formulas
Range	Formula
B1	B1	=MATCH(A1,Table1[#Headers],0)
A2:A10	A2	=IFERROR(INDEX(Table1,ROWS(A$2:A2),B$1)&"","")

Perfect thank you so much again!

 

[Peter_SSs]

No problem.