Multiple assigned to with different workload

groszbela

Hello,
I want to assign different workloads to people who are members of projects, but I am unable to figure out how to do that.
For example I would like to set up:

Project A has 2 members:
Member A with 5% capacity
Member B with 15% capacity

thanks in advance

jmyzk_cloudsmart_jp

Hi @groszbela

To achieve this in your project, you can use two sheets: one for capacity (Capacity Sheet) and another for projects (Project Sheet). In the Capacity Sheet, each member's capacity is defined. In the Project Sheet, for each task, you can add child rows to assign contacts like A, B, etc., and use formulas to proportionally allocate tasks based on their capacity.

https://app.smartsheet.com/b/publish?EQBCT=58987ec4bdf64c748be7b1671644c645 (Link to the published demo sheet: Capacity Sheet)

https://app.smartsheet.com/b/publish?EQBCT=5be474d2fd8647238c00d360f4a97fde (Link to the published demo sheet: Project Sheet)

Step 1: Getting the Denominator from the Capacity Sheet

In the Project Sheet, you need to calculate the denominator (i.e., the total capacity %) for each parent row. Use the following formula to achieve this:

=IF([Is Parent]@row, SUM(COLLECT({Capacity Range : Capacity}, {Capacity Range : Assigned To}, HAS(CHILDREN([Assigned To]@row), @cell))), "")

Here, [Is Parent] is a checkbox that determines if the row is a parent. The formula collects the total capacity of all members assigned to the child rows under that parent task.

Step 2: Allocating Tasks Proportionally

To distribute the task workload proportionally to the assigned members based on their capacity, use the following formula in the Allocation % column:

=IF([Is Parent]@row, "", IFERROR(INDEX(COLLECT({Capacity Range : Capacity}, {Capacity Range : Assigned To}, [Assigned To]@row), 1) / PARENT(Denominator@row), ""))

This formula checks if the row is a parent row; if not, it calculates the allocation percentage by dividing each assigned member's capacity by the parent row's total capacity (denominator).

Note on Capacity Updates

As new values are entered in the Allocation % column, the capacity values for each member in the Capacity Sheet should dynamically change. However, directly modifying the capacity values based on these allocations could create circular references, leading to errors.

To avoid this, you might consider adding a helper column in the Capacity Sheet to reference updated capacity values from the Project Sheet. This approach can help review and update capacities without causing circular reference errors. However, for simplicity, this method has been omitted here.

By following this approach, you can dynamically and proportionally allocate tasks based on members' capacities without causing circular references in your Smartsheet formulas.