Need help with simple formula using two tables

1

Hi,

This should be easy, but I suck at programming.
I am trying to setup a Team Resource planner doc.

I have one table with a bunch of tasks, planned into sprints, estimated and assigned to a team member.
Then I have another table in which I list every sprint, and the capacity for each team member that sprint.

Now I would like to add a column (“Remaining Work Days: Philip”) in the second table that shows:

  • The current capacity this sprint for this member
  • Minus the total estimated effort for all tasks (in the other table) that have the same Sprint Name and Team Member as this.Row

The goal is to see when I have reached the limit for each team member for each sprint.
I’d appreciate any help, and also if you have any thoughts on general improvements to the doc!

2

Hi @Rickard_Asen ,

I added the formula in the Remaining Work Days: Philip column, but be aware that your doc structure only works for a very small team, as you would have to add multiple columns for each new member. Try using more lookups and groupings, as an example, I added a sum to the estimated effort row as an which also shows you the sum for each group.