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.

ProjectbyBT_User

We request the ability to set distinct % Allocation values for each resource assigned to a single task without requiring workarounds such as duplicating task rows or creating child tasks. In one use case, a project has over 60 tasks and some tasks have 7-9 resources assigned to it. If we use the duplicate or child task approach, we will have over 300 tasks instead of the 60 real tasks in the plan.

We are a Smartsheet partner, and our clients use Smartsheet to manage their projects within our application. Many of our large clients have specifically requested this feature. Please see the attached document for more details. This functionality is a standard feature in Microsoft Project, Asana, and Planview.

Product Feature Request - Smartsheet.docx.pdf

Georgie

Hi @ProjectbyBT_User,

Thanks for your feedback! In order for other users to add their votes and comments to ideas for features, suggestions/requests for features need to be posted in the Smartsheet Product Feedback and Ideas topic. 

I found an existing idea for this feature here: 

• Multiple resource allocations with different % per task. 

I can see that you have already added your comment and your document with further explanation on the above thread - thank you! Have you also added your vote to that idea (by clicking the arrow on the “idea submitted” banner)?

Additionally, I wanted to post the link here so that other members seeing this thread and wanting the same feature can open the link above and add their votes.

Thanks,

Georgie