Creating a list of values based on dropdown/combobox selection

[piannetta]

Hi everyone,

I have a comboxbox on a spreadsheet that is populated with a list of managers. What I'm trying to achieve is to create a list of employees reporting to that manager from a master list of about 150 employees. Then if a different manager is selected from the dropdown, the resulting list of employees changes.

I'm struggling to figure out how to do this and hoping I could get some tips/ideas please?

Cheers,
Pete

 

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

I have a comboxbox

ActiveX or Forms?
Linked cell?

master list of about 150 employees.

Does that have employee in the left column and manager in the right column? If not, details please (or small sample with XL2BB)

 

[piannetta]

Apologies, I have updated my profile and installed XL2BB

It's a form control

So I have a Home page where the control exists (which XL2BB won't include in the screenshot). What I'm hoping to achieve is that when a Team Leader is selected from the form control, in the cells beneath it I want a list of their employees to be populated.

The employee list looks like this:

Daisee Score Tool v1.0.xlsm
	A	B	C	D	E
3	Agent Name	TL	Location	Average QA Score	# QA >= Target
4				No QA Found	No QA Found
5				No QA Found	No QA Found
6				No QA Found	No QA Found
7				No QA Found	No QA Found
Results

Cell Formulas
Range	Formula
D4:D7	D4	=IFERROR((SUMIF(DaiseeExport!$B$2:$B$777299,Results!A4,DaiseeExport!$K$2:$K$777299)/COUNTIF(DaiseeExport!$B$2:$B$777299,Results!A4)),"No QA Found")
E4:E7	E4	=IFERROR((COUNTIFS(DaiseeExport!$B$2:$B$7297,Results!A4,DaiseeExport!$K$2:$K$7297,">="&Results!$B$1)/COUNTIFS(DaiseeExport!$B$2:$B$7297,Results!A4)),"No QA Found")

The Team Leaders are in a separate list:

Daisee Score Tool v1.0.xlsm
	P	Q
3	1
4	2
5	3
Results

Cheers,
Pete

 

[Peter_SSs]

Thanks for updating your version profile and getting XL2BB going.

It's a form control

Thanks.

Nothing seems to answer this though.

Linked cell?

That is, what is the linked cell of the combo box? The cell that is actually populated with the Manager.

The employee list looks like this:

I cannot see anything that looks like an employee on that mini sheet.
In any case, it did not answer this

Does that (master list) have employee in the left column and manager in the right column? If not, details please (or small sample with XL2BB)

When I eventually see a managers name or ID or whatever it is, how am I going to locate the employees that report to that manager?

 

[piannetta]

Thanks for updating your version profile and getting XL2BB going.

Thanks.

Nothing seems to answer this though.

The linked cell is just a cell that has the Team Leader selection made on the combobox, it returns the position on the list, I then do a lookup to get the Team Leader name, and that's what I'd use to find employees on the employee list assigned to that leader.

Daisee Score Tool v1.0.xlsm
	P	Q	R	S
1		7	Marcus Singh
2
Results

Cell Formulas
Range	Formula
R1	R1	=VLOOKUP(Q1,P3:Q36,2,0)

That is, what is the linked cell of the combo box? The cell that is actually populated with the Manager.


I cannot see anything that looks like an employee on that mini sheet.
In any case, it did not answer this

The column "Agent Name" is the employee name.

When I eventually see a managers name or ID or whatever it is, how am I going to locate the employees that report to that manager?

That's the root of my question. Essentially what I'm asking is, is there a way in Excel that I can build a dynamic list based on the entry selected in the combbox and find entries in the employee list that match the combobox selection. I'm trying to determine if there's a way to do that other than writing some VBA code (which I'm fine to do) but hoping not to have to if there was a quicker (out of the box) option.

 

[Peter_SSs]

The linked cell is just a cell that has the Team Leader selection made on the combobox, it returns the position on the list, I then do a lookup to get the Team Leader name, and that's what I'd use to find employees on the employee list assigned to that leader.

OK, so from your mini sheet it looks like the linked cell is Q1 (& R1 gets the name via VLOOKUP). That' helpful, thanks.

Essentially what I'm asking is, is there a way in Excel that I can build a dynamic list based on the entry selected in the combbox and find entries in the employee list that match the combobox selection. I'm trying to determine if there's a way to do that other than writing some VBA code

Yes there is, but to suggest exactly how, I need to know where the employee list is and what it looks like so I can work out how a particular employee is associated with a particular team leader
For example your list might look like this

piannetta.xlsm
	A	B
1	Employee Name	Team Leader
2	Emp 1	Ann
3	Emp 2	Tom
4	Emp 3	Ann
5	Emp 4	Ann
6	Emp 5	Tom
7	Emp 6	Ann
8	Emp 7	Ann
9	Emp 8	Tom
10	Emp 9	Ben
Sheet2

.. or it might look like this

piannetta.xlsm
	E	F	G	H	I	J	K	L
1	Team Leader ID	Team Leader Name	Start Date	Employees
2	1	Tom	1/05/2020	Emp 2	Emp 5	Emp 8
3	2	Ann	3/07/2018	Emp 1	Emp 3	Emp 4	Emp 6	Emp 7
4	3	Ben	5/07/2022	Emp 9
Sheet2

.. or any one of a myriad different ways.
It is no use me guessing so I'm asking for details or a sample mini sheet

 

[piannetta]

piannetta.xlsm
	A	B
1	Employee Name	Team Leader
2	Emp 1	Ann
3	Emp 2	Tom
4	Emp 3	Ann
5	Emp 4	Ann
6	Emp 5	Tom
7	Emp 6	Ann
8	Emp 7	Ann
9	Emp 8	Tom
10	Emp 9	Ben
Sheet2

It looks like the above

 

[Peter_SSs]

It looks like the above

Thanks. In that case try this.

piannetta.xlsm
	A	B																R
1	Employee Name	Team Leader																Tom
2	Emp 1	Ann																Emp 2
3	Emp 2	Tom																Emp 5
4	Emp 3	Ann																Emp 8
5	Emp 4	Ann
6	Emp 5	Tom
7	Emp 6	Ann
8	Emp 7	Ann
9	Emp 8	Tom
10	Emp 9	Ben
Sheet2

Cell Formulas
Range	Formula
R2:R4	R2	=FILTER(A2:A10,B2:B10=R1)
Dynamic array formulas.

 

[piannetta]

Thanks. In that case try this.

piannetta.xlsm
	A	B																R
1	Employee Name	Team Leader																Tom
2	Emp 1	Ann																Emp 2
3	Emp 2	Tom																Emp 5
4	Emp 3	Ann																Emp 8
5	Emp 4	Ann
6	Emp 5	Tom
7	Emp 6	Ann
8	Emp 7	Ann
9	Emp 8	Tom
10	Emp 9	Ben
Sheet2

Cell Formulas
Range	Formula
R2:R4	R2	=FILTER(A2:A10,B2:B10=R1)
Dynamic array formulas.

Thanks Peter, this works perfectly, I was unaware of this function until now and it's been an eye opener. I appreciate your patience working with me and in helping me find the perfect solution.

Cheers,
Pete

 

[Peter_SSs]

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