List users based on criteria
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?
- How do I get a list of members in the Mgmt or Ops Team?
- How do I get a list of Managers with over 10+ years of experience?
Appreciate your input!
Thanks in advance.
Best Answers
-
- Try this:
- =JOIN(COLLECT([Member Name]:[Member Name], Dept:Dept, "Ops"), ",")
- Replace "Dept:Dept" with whatever that column is.
- Try this:
- =JOIN(COLLECT([Member Name]:[Member Name], Dept:Dept, "Management", [Years of Experience]:[Years of Experience], >=10), ",")
- Replace "Dept:Dept" with whatever that column is.
- Try this:
-
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), "")
Answers
-
- Try this:
- =JOIN(COLLECT([Member Name]:[Member Name], Dept:Dept, "Ops"), ",")
- Replace "Dept:Dept" with whatever that column is.
- Try this:
- =JOIN(COLLECT([Member Name]:[Member Name], Dept:Dept, "Management", [Years of Experience]:[Years of Experience], >=10), ",")
- Replace "Dept:Dept" with whatever that column is.
- Try this:
-
How exactly are you wanting to use these lists?
-
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!
-
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), "")
-
Agree with Paul. Have to add an index the list.
-
@Paul Newcome @Matt Lynn-PCG - that is exactly what I was looking for!
Thank you so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!