I am looking for a formula to pull specifics from a range of data (see screen shot attached)

The goal would be to pull in "For the role of Market Lead, here is the list of systems that role will need access to: (Example: Director of Operations will need FIS Client Portal and Black Diamond). From there, I will index/match the correct system owner and set up a workflow to request that they provide the needed system access. I just can't get past the first step of how to efficiently gather a list of which systems are needed for which role. This doesn't seem that difficult but I'm over thinking it I think. Any light anyone can shed is greatly appreciated. (I'm hoping this particular project will FINALLY catch the eye of leadership and help plead my case that we need to leverage SS much more than we currently do :) -Shelley


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭


    I hope you're well and safe!

    Another way to structure it could be to use a multi-select column instead of breaking them out.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


    Thanks Andree for the quick response...Is there a way to use a formula in another sheet to pull in the column name for each cell that contains an 'x' (if of course I went back and added the column names across Row 1 instead of just in the column headers)? At first I thought an index/join may help but it brings back the all of the column names into one cell which is a problem. I need each System Name to come back into an individual cell because it will have a unique contact person next to it (the System Owner). I would then set up a workflow to send that System Owner an update request asking them to set up the access for the new hire. My current set up is below and it works ok, BUT, in the likely event we have to for instance, change our policy so that a Credit Analyst doesn't need access to xyz system any longer, I can just remove the 'x' from the cell in the master sheet under xyz system, rather than adjusting the formula below. I feel like it's too much of a margin for error to adjust the formula each time a change is made rather than add or remove an 'x' from the master sheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!