I’m developing an annual planning tool for a multi-country project to be used by a team of 20 persons.
Each person will select a key activity from a list and the country for which that activity is being planned.
The list of activities are numbered as follows: 1111, 1112, 1113, 1121, 1122… etc.
I want to have a column that counts the number of sub-activities under each activity, and the numbering restarting at 1 for each country. Something like this
For country A:
For country B
What I can’t figure out in Coda (although I managed to do it in Excel) is how to increment the counter (the decimal part) at each line while the country and the Activity remain the same and then start at 1 if any of these change.
Any ideas or help on a formula for this will be truly appreciated.
Many thanks in advance
hi @Gerardo_Ducos ,
I am not a Codan and understood only partly your question. Does the feedback help?
it is not comparable with any spreadsheet, but to have something, consider this option, value for new row:
Hi @Gerardo_Ducos ,
I’m not sure if I understood correctly your setup but here’s what I have
To create an incremental count of something in Coda, you need to have an increment somewhere we can use to compare rows between each others …
This can be done by creating a row index/number (which is what I did here) or another row property such as (
Created (which I don’t have the time to explore here ))
In the sample below, you’ll see that I added a column (called
[Row Number]) which find
thisRow in the
Table (quite literally) :
Then, I added another column called
[Sub-Activity Decimal] which is just there to create the incremental count I think you’re looking for …
And the formula is :
AND [Row Number] <= thisRow.[Row Number]
And what it does is :
It takes the whole list of rows (each row being stored as
CurrentValue) in the table
Table ( ) and looks for the rows where :
(This returns (behind the scene) a 1st list of values)
(From the 1st list of values,
Filter() only keeps the rows where this condition is
true … Which “becomes” a 2nd list of values)
CurrentValue.[Row Number] is less or equal to
From the 2nd list of values,
Filter() only keeps now the rows where this condition is
true and this “creates” an incremental list of values …
As for the very first
Sub-activity of the very first
Country A it will only find 1 row … 2 for the second activity… etc …
All that’s left is to
Count() the values returned by the
Filter() to have the incremental count of
As for the
Sub-Activity column, I created them with this formula :
Which simply creates the
Sub-activity xxxx.x using the various info from the columns I already have
(But there are other ways to get to that result (i.e.:
Now depending on your actual setup (and how you linked your datas to each others or if you did), this might need to be adapted … But I hope this helps
No wonder you’re a “Community Champion”.
The solution you proposed works flawlessly. Thank you very much for taking the time and sharing!
This will allow me to create a gantt (timeline) where I can have a global overview or a view per country of respective sub-activities with the proper numbering.
No problem @Gerardo_Ducos !
I’m very glad to know this works for you !
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.