List users based on criteria

PerDeSi

Hi Smartsheet Community,

I need to generate a list of names based on multiple criterias, can you kindly point me in the direction on how to accomplish this?

1. How do I get a list of members in the Mgmt or Ops Team?
2. How do I get a list of Managers with over 10+ years of experience?

Appreciate your input!

Thanks in advance.

Matt Lynn-PCG

1. Try this:
   1. =JOIN(COLLECT([Member Name]:[Member Name], Dept:Dept, "Ops"), ",")
   2. Replace "Dept:Dept" with whatever that column is.
2. Try this:
   1. =JOIN(COLLECT([Member Name]:[Member Name], Dept:Dept, "Management", [Years of Experience]:[Years of Experience], >=10), ",")
   2. Replace "Dept:Dept" with whatever that column is.

@PerDeSi

Paul Newcome

You will need to insert a text/number column (called "Number" in this example) and then manually enter the numbers starting with one and going as far down as the last entry in your Member Name column.

1

2

3

4

5

etc.

Then you would change the JOIN function to an INDEX function, the delimiter to Number@row and wrap it in an IFERROR. The COLLECT portion won't change.

=IFERROR(INDEX(COLLECT(…………….), Number@row), "")

Matt Lynn-PCG

1. Try this:
   1. =JOIN(COLLECT([Member Name]:[Member Name], Dept:Dept, "Ops"), ",")
   2. Replace "Dept:Dept" with whatever that column is.
2. Try this:
   1. =JOIN(COLLECT([Member Name]:[Member Name], Dept:Dept, "Management", [Years of Experience]:[Years of Experience], >=10), ",")
   2. Replace "Dept:Dept" with whatever that column is.

@PerDeSi

Paul Newcome

How exactly are you wanting to use these lists?

PerDeSi

Thank you @Matt Lynn-PCG & @Paul Newcome for your input.

Although I am getting the correct results with @Matt Lynn-PCG, I need to the results to be returned into individual cells so I can use the results for individual formulas.

With the JOIN formula:

Expected results:

Thank you!

Paul Newcome

You will need to insert a text/number column (called "Number" in this example) and then manually enter the numbers starting with one and going as far down as the last entry in your Member Name column.

1

2

3

4

5

etc.

Then you would change the JOIN function to an INDEX function, the delimiter to Number@row and wrap it in an IFERROR. The COLLECT portion won't change.

=IFERROR(INDEX(COLLECT(…………….), Number@row), "")

Matt Lynn-PCG

Agree with Paul. Have to add an index the list.

PerDeSi

@Paul Newcome @Matt Lynn-PCG - that is exactly what I was looking for!

Thank you so much!

Paul Newcome

Happy to help. 👍️