Search for assigned manager, then get list of direct reports

Options
Mary A
Mary A ✭✭
edited 10/23/20 in Formulas and Functions

Hi - I am trying to create a formula in a Contact List column that returns all the people reporting to a manager.

I do not know the best way to build this formula. Would someone kindly help me with the best way to do this, and possibly get me started with a formula?

In the images below is my prototype. I have 5 employees total, and 2 are managers (Amy & Emmit).

In the "Lab Members" column, I would like to have a formula search the "Manager (PI)" column for the name of the Manager (Amy & Emmit in this example), and then return all of the employees the from the Contact List in that cell.


I would like to go from this -

To this - where if I look at Amy or Emmit's "Lab Members" column, there is a list of all their employees from the Contact List.


Answers

  • Jenna Bailey
    Jenna Bailey ✭✭✭✭✭
    Options

    I would create a separate sheet where all of this is listed where the "Manager" is in column 1 and the "Lab Member" is in column 2 then in your main source sheet do a vlookup that returns the value of column 2

  • Mary A
    Mary A ✭✭
    Options

    Thanks. That is unfortunately not a viable solution because that means I would have to keep 2 sheets with "Manager" and "Lab Member" names updated. The reason this comes up in the first place is that managers and members change often, and when new "lab members" join, it is all entered on one sheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!