How to list employees based on manager

jbaranski

I have a list of managers and their employees on a data sheet. I then have a template sheet that will be used by the managers to fill in the time spent by each of their employees in a month.

I would like to be able to select a manager on the template sheet and have all of their employees populated in the rows below using formulas. VLOOKUP will only return the first match so that's not too helpful.

Let me know if screenshots would help.

Paul Newcome

You are going to need a text/number column (called "Entry" in this example) manually populated from 1 to whatever to accommodate the highest number of employees you will need plus a buffer just in case. So if the manager with the most employees has 50 employees, I would suggest filling it to 75.

Then to get the list populated (assuming you are selecting the manager in a Sheet Summary field), you would use:

=IFERROR(INDEX(COLLECT({Source Sheet Employee Column}, {Source Sheet Manager Column}, @cell = [Manager Name]#), Entry@row), "")

Paul Newcome

The Entry column goes in the template and [Manager Name]# is referencing a sheet summary field that has your manager's name in it (also in the template).

Paul Newcome

You are going to need a text/number column (called "Entry" in this example) manually populated from 1 to whatever to accommodate the highest number of employees you will need plus a buffer just in case. So if the manager with the most employees has 50 employees, I would suggest filling it to 75.

Then to get the list populated (assuming you are selecting the manager in a Sheet Summary field), you would use:

=IFERROR(INDEX(COLLECT({Source Sheet Employee Column}, {Source Sheet Manager Column}, @cell = [Manager Name]#), Entry@row), "")

jbaranski

Thanks for the help but I'm a little confused, tried to adapt your formula but getting an unparsable error.

Is the Entry column in the source sheet or the template where the formula is?

What is the [Manager Name]# referencing?

Here is the formula I'm using where Manager is the column name of the manager and ID is the column with the numbers.

=IFERROR(INDEX(COLLECT({Employee Range}, {Manager Range}, @cell = [Manager]#), ID@row), "")

Paul Newcome

The Entry column goes in the template and [Manager Name]# is referencing a sheet summary field that has your manager's name in it (also in the template).

jbaranski

That worked perfectly thanks!

Paul Newcome

Happy to help. 👍️