Need formula to pull in names associated with RACI roles.

rmc0030

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:

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.

Any guidance is appreciated!

Julio S.

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. 

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([email protected], {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([email protected], {Clients})) with MATCH([email protected], "Name of the relevant Client")) for example

FORMULA:

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

RESULT:

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.

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. 

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([email protected], {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([email protected], {Clients})) with MATCH([email protected], "Name of the relevant Client")) for example

FORMULA:

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

RESULT:

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

rmc0030

Thanks so much, Julio! This was incredibly helpful.