Project Plan  RACI Formula Automation
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 (RAssigned To, AAssigned 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?
Best Answer

Hi @rmc0030
I can seen that at least your "R" Column is now a multidropdown 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
Answers

Hi @rmc0030
I can seen that at least your "R" Column is now a multidropdown 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. , you are a lifesaver! Thanks so much for all of your help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!