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 (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?
Best Answer
-
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
Answers
-
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. , you are a lifesaver! Thanks so much for all of your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!