Good Morning All
I've searched the forum using Google and found two posts associated with RACI charts (aka RAM Chart), however they don't handle my situation.
I am trying to go from a Work Breakdown Structure (WBS) developed in MS Project 2010 on a Windows 7 platform to a RAM Chart in Excel 2010. The required indexing, lookups and formatting is well beyond my comfort level / skills in Excel.
Within MS Project, I develop a task list (Text) and use a number of Custom Text Fields to list the people involved in a specific role.
The extract from Project looks like this
<tbody>
</tbody>
A properly formatted RAM Chart would look like:
<tbody>
</tbody>
So I’m looking for the appropriate macro (?) that:
1) Identify each individual name in the first table (R2C2:RnCn) and then place each individual as a Column Header in the RAM Chart table.
a. Condition: Row count needs to be calculated since I don’t know how long the Task Column will be for a project.
b. Condition: Ideally, cells with multiple people would be “split” / counted for how many “new” names are in the cell and each “new” name used a column header. If this can’t be accomplished then all the names in a single cell could be used as a column header. Within the original project file, there should always be at least one resource name in the Responsible column since this is field used to assign resources to the Task in MS Project. It is possible and also likely that there will be multiple names separated by a comma within Responsible. Column count in the RAM chart will be dependent on how many individual (or group) names are identified.
2) Place under each person’s name (or group of names), the appropriate role.
a. Condition: Since we haven’t settled on a standard for role names, the column count from Column B to the right-most column in the original table will be needed. Some styles of RACI charts use up to six roles.
b. Condition: Where a person is both Responsible and Accountable, the higher cell importance (Accountable) should be entered.
Since the purpose of the RAM chart is to clarify roles / responsibilities and conflicts, ideally conditional formatting would be used to identify:
Tasks where there is no designated Responsible to confirm that the Accountable is actually doing the work plus correct the original MS Project file Resource Assignment.
Tasks where there are multiple Accountable.
Extra bonus question:
If I had a properly formatted RAM Chart in Excel already, how could I go “back” to the WBS Table with Roles as Column headers and Cells containing lists of people? If a list in a single cell would be too hard, additional rows with the same Task Name (or blanked) would be useful.
Rereading this, it seems quite a wish list, but I am hoping that someone can help.
Cheers
Bob Lohmaier
Ottawa, ON
I've searched the forum using Google and found two posts associated with RACI charts (aka RAM Chart), however they don't handle my situation.
I am trying to go from a Work Breakdown Structure (WBS) developed in MS Project 2010 on a Windows 7 platform to a RAM Chart in Excel 2010. The required indexing, lookups and formatting is well beyond my comfort level / skills in Excel.
Within MS Project, I develop a task list (Text) and use a number of Custom Text Fields to list the people involved in a specific role.
The extract from Project looks like this
Task Name | Responsible | Accountable | Consulted | Informed |
Job 1 | Bill, Mark | Susan | | Joe |
Job 2 | Susan | Mark | Steven, Bill | Joe |
Job 3 | Bill | Bill | Susan | Mark |
…..etc. | | | | |
Job X | Steven | Mark, Susan | | Bill, Joe |
<tbody>
</tbody>
A properly formatted RAM Chart would look like:
Task Name | Bill | Mark | Steven | Joe | Susan |
Job 1 | Responsible | Responsible | | Informed | Accountable |
Job 2 | Consulted | Accountable | Consulted | Informed | Responsible |
Job 3 | Accountable | Informed | | | Consulted |
…..etc. | | | | | |
Job X | Informed | Accountable | Responsible | Informed | Accountable |
<tbody>
</tbody>
So I’m looking for the appropriate macro (?) that:
1) Identify each individual name in the first table (R2C2:RnCn) and then place each individual as a Column Header in the RAM Chart table.
a. Condition: Row count needs to be calculated since I don’t know how long the Task Column will be for a project.
b. Condition: Ideally, cells with multiple people would be “split” / counted for how many “new” names are in the cell and each “new” name used a column header. If this can’t be accomplished then all the names in a single cell could be used as a column header. Within the original project file, there should always be at least one resource name in the Responsible column since this is field used to assign resources to the Task in MS Project. It is possible and also likely that there will be multiple names separated by a comma within Responsible. Column count in the RAM chart will be dependent on how many individual (or group) names are identified.
2) Place under each person’s name (or group of names), the appropriate role.
a. Condition: Since we haven’t settled on a standard for role names, the column count from Column B to the right-most column in the original table will be needed. Some styles of RACI charts use up to six roles.
b. Condition: Where a person is both Responsible and Accountable, the higher cell importance (Accountable) should be entered.
Since the purpose of the RAM chart is to clarify roles / responsibilities and conflicts, ideally conditional formatting would be used to identify:
Tasks where there is no designated Responsible to confirm that the Accountable is actually doing the work plus correct the original MS Project file Resource Assignment.
Tasks where there are multiple Accountable.
Extra bonus question:
If I had a properly formatted RAM Chart in Excel already, how could I go “back” to the WBS Table with Roles as Column headers and Cells containing lists of people? If a list in a single cell would be too hard, additional rows with the same Task Name (or blanked) would be useful.
Rereading this, it seems quite a wish list, but I am hoping that someone can help.
Cheers
Bob Lohmaier
Ottawa, ON