Project Plan - RACI Formula Automation

r0030

I have a Project Plan template that will be customized for each client each time it is saved as new. At the top we've included a Stakeholder list (Exec Sponsor, PMO Project Lead, Program Lead, & SDC). I'd like to automate the RACI columns in my Project Plan to correlate with the stakeholder list.

The roles themselves within the RACI can change quite often.

I created 4 helper columns (R,A,C,I) that display the role associated with each task, and then the main RACI columns (R-Assigned To, A-Assigned To, etc.) have a formula that pulls from the stakeholder list up top.

The problem that I'm running into is that sometimes there are multiple roles for each. For example, the PMO Project Lead and the Program Lead may both be responsible for the task on row 8. I'm unsure how to account for that, as the current formula I have only pulls in one contact per field. (=IF(R@row = "PMO Project Lead", Reference$3, IF(R@row = "SDC", Reference$5, IF(R@row = "Program Lead", Reference$4))))

Could anyone help?

Julio S.

Hi @rmc0030

I can seen that at least your "R" Column is now a multi-dropdown column. Did you try with the formula that I suggested in your previous post?

=IF(HAS(R@row, "PMO Project Lead"), Reference$2) + CHAR(10) + IF(HAS(R@row, "Program Lead"), Reference$3) + CHAR(10) + IF(HAS(R@row, "SDC"), Reference$4)

Note how adding "+" as separators for each IF() formula will aggregate the results instead of stopping the formula at the first match found. The additional CHAR(10) will make each result independent. Introducing the HAS Function will help searching for the specific sequence of characters within the cell. The only drawback is that the HAS() function will return text strings instead of contacts.

I hope this can be of help.

Cheers!

Julio

Julio S.

Hi @rmc0030

I can seen that at least your "R" Column is now a multi-dropdown column. Did you try with the formula that I suggested in your previous post?

=IF(HAS(R@row, "PMO Project Lead"), Reference$2) + CHAR(10) + IF(HAS(R@row, "Program Lead"), Reference$3) + CHAR(10) + IF(HAS(R@row, "SDC"), Reference$4)

Note how adding "+" as separators for each IF() formula will aggregate the results instead of stopping the formula at the first match found. The additional CHAR(10) will make each result independent. Introducing the HAS Function will help searching for the specific sequence of characters within the cell. The only drawback is that the HAS() function will return text strings instead of contacts.

I hope this can be of help.

Cheers!

Julio

r0030

@Julio S. , you are a lifesaver! Thanks so much for all of your help.