Need formula to pull in names associated with RACI roles.

Options
r0030
r0030 ✭✭✭✭

I have a Project Plan template that I would like my team members to save as new for each client and customize the information. Within the Project Plan, there's a RACI matrix with different roles. Please see below for an example:

image.png


When a new client is assigned and team members save the project template as new, I'd like to be able to automatically populate the employee names associated with the RACI roles. I started to create a reference sheet below, but not sure what function I'll use to pull in the names.

image.png

Any guidance is appreciated!

Best Answers

  • Julio S.
    Julio S. Employee
    edited 05/21/22 Answer βœ“

    Hi @rmc0030,

    It looks like usingΒ INDEX(MATCH()) formulas should accomplish what you intend once you have filled each pair of client names and specific roles as shown below.Β 

    Screenshot 2022-05-21 at 10.16.33.png

    To bring out each of the relevant employees assigned to a client and role, you can use formulas in line with:Β =INDEX({PMO Project Lead}, MATCH(Clients@row, {Clients})). If you are looking to bring out multiple roles for each Column in a multi-select dropdown, you may want to use CHAR(10) to separate different formulas for each role so that these can form separate dropdown values. If the "Client Column" isn't present in each Project sheet, you may substituteΒ MATCH(Clients@row, {Clients})) with MATCH(Clients@row, "Name of the relevant Client")) for example

    FORMULA:

    Screenshot 2022-05-21 at 10.20.58.png

    *Note that each reference between {} is aΒ cross sheet referenceΒ to the columns with the same name in the source.

    RESULT:

    Screenshot 2022-05-21 at 10.20.25.png

    Since each Column seems to have role inconsistent role assignations, it looks like it might be less time consuming simplyΒ updating each Dropdown columnΒ with all names from the matrix and manually selecting them rather by using formulas. Otherwise, you will need to manually insert the relevant formula for each role assignment combination.

    I hope this can offer some guidance that can help you with what you are trying to achieve.

    Cheers!

    Julio

  • Julio S.
    Julio S. Employee
    edited 07/09/22 Answer βœ“

    Hi @rmc0030,

    You may try converting your RACI columns to multi select dropdown columns and using the HAS() function. Then adding "+" to separate each IF statement and CHAR(10) to separate each returned value. Note, however, that the results will be returned as text instead of contacts, even if the lookup values under "reference" are actual Contacts.

    Screenshot 2022-07-09 at 09.23.02.png


    I hope that this can be helpful.

    Cheers!

    Julio

Answers

  • Julio S.
    Julio S. Employee
    edited 05/21/22 Answer βœ“

    Hi @rmc0030,

    It looks like usingΒ INDEX(MATCH()) formulas should accomplish what you intend once you have filled each pair of client names and specific roles as shown below.Β 

    Screenshot 2022-05-21 at 10.16.33.png

    To bring out each of the relevant employees assigned to a client and role, you can use formulas in line with:Β =INDEX({PMO Project Lead}, MATCH(Clients@row, {Clients})). If you are looking to bring out multiple roles for each Column in a multi-select dropdown, you may want to use CHAR(10) to separate different formulas for each role so that these can form separate dropdown values. If the "Client Column" isn't present in each Project sheet, you may substituteΒ MATCH(Clients@row, {Clients})) with MATCH(Clients@row, "Name of the relevant Client")) for example

    FORMULA:

    Screenshot 2022-05-21 at 10.20.58.png

    *Note that each reference between {} is aΒ cross sheet referenceΒ to the columns with the same name in the source.

    RESULT:

    Screenshot 2022-05-21 at 10.20.25.png

    Since each Column seems to have role inconsistent role assignations, it looks like it might be less time consuming simplyΒ updating each Dropdown columnΒ with all names from the matrix and manually selecting them rather by using formulas. Otherwise, you will need to manually insert the relevant formula for each role assignment combination.

    I hope this can offer some guidance that can help you with what you are trying to achieve.

    Cheers!

    Julio

  • r0030
    r0030 ✭✭✭✭

    Thanks so much, Julio! This was incredibly helpful.

  • r0030
    r0030 ✭✭✭✭

    Hi @Julio S. - circling back to this as it has evolved a bit. :) Instead of having a separate column per row like in the previous screenshot, I've created a small stakeholder list at the top of the project plan. See below:

    image.png

    The Reference column shown above (Person 1, Person 2) is a contact column that we'll use to record team members once they're assigned to a project.


    I then created 4 helper columns (R,A,C,I) where the role is specified. See below:

    image.png


    Lastly, I created 4 actual RACI columns (R-Assigned To, A-Assigned To, etc.) and inserted a formula to pull in the contact associated with the role notated in the RACI helper column.

    Smartsheet.png

    This worked perfectly for this particular use case. However, I'm now realizing that there could potentially be multiple roles in the R,A,C,I helper columns, and my formula does not pull in multiple contacts in my actual RACI columns.


    I've tried every INDEX/MATCH and VLOOKUP formula I can find, but am totally stumped. Do you know how I can accomplish pulling in multiple contacts based on the role & stakeholder list within my project plan?

  • Julio S.
    Julio S. Employee
    edited 07/09/22 Answer βœ“

    Hi @rmc0030,

    You may try converting your RACI columns to multi select dropdown columns and using the HAS() function. Then adding "+" to separate each IF statement and CHAR(10) to separate each returned value. Note, however, that the results will be returned as text instead of contacts, even if the lookup values under "reference" are actual Contacts.

    Screenshot 2022-07-09 at 09.23.02.png


    I hope that this can be helpful.

    Cheers!

    Julio

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!