VLookup table - summing values and linking to another table

1

Hi Coda Community!

I’m trying to essentially do a Vlookup on my Coda table and have no idea how to do this.

I have a source data table with 3 columns e.g.

Task 1 Jack 2 hours
Task 2 Amanda 3 hours
Task 3 Jack 3 hours
Task 4 Mike 1 hour
Task 5 Amanda 6 hours

I would like to summarise this data into a 2-column table (example below) which automatically sums up the hours for Jack, Amanda and Mike so that when I change the data in the above table (e.g. add more tasks or change hours) the table below is automatically updated.

Jack 5 hours
Amanda 9 hours
Mike 1 hour

Grateful for any help please! Thanks!!

2

Apologies for the lack of column differentiation :smiley:

3

Hello @Haoqi_Pan and welcome to Coda.

What you are trying to do is very simple with coda.

First you have to create a table with the team (it is your second table). One column of type “text” with the name of the team member and a second column of type “duration” with the sum of the hours. Than create a task table, with the three columns that you mentioned, but the member team column will be relation type, which will relate to the team table.

Back to the team table, add a formula to total hours columns, something like below and you are ready to go:

Tasks.Filter(Member=thisRow).Duration.Sum()

Here is an doc example with your data:

4

Thank you so much Felipe! I got there in the end with your worked example :slight_smile: Appreciate it!

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.