I’m looking to build a table for an event agenda. I want to have 3 Columns, Start Time, End Time, and Duration.
When I update the duration it automatically updates the end time by adding the duration to the start time. But I also want it to update the start time on the next row.
Does the next row exist, or will it be created as part of this update? I assume with a button?
If it is a new row, you simply use the addRow formula. If it already exists, you first need to find the “next” row. And before you find the next row, you first need to define what “next” row means. Is it in some date/time sequence? (Since you’re looking to add the time, I assume that there is no date/time to sort on yet.) Is it the sequence in which rows were created?
The following information explains more on how to find “next” row, if that is indeed what you are trying to do.
Adding a Time Order Column allows you to reference other rows based on the current row. because start time is a formula, we need start time override to designate a start time. this override column has the added benefit of manually choosing a different start time for each row.
notice the Time Order column has a formula for value for new rows.
You talk about an agenda, there must be dates as well,. Then you have numbers you can calculate with. As you may know, dates and time are both numbers you can compare using a filter and logical operators. That is what you should do.
I am out today, others will help you to see through, cheers, Christiaan
The short answer: I think ik can be done.
The longer answer: it can get complicated in a hurry. I imagine that the start time is entered manually, therefore, you cannot have a formula in the same column that acts upon a change in a previous row.
I think you are going to need a helper column and an automation. The helper column will actually hold calculated start times, and the automation will replace the manually entered start times.
What you have to think about: it is possible that you might need to adjust more than one row, if there are more rows following upon the changed row. You also need to think about what you want to happen when there is a gap in your schedule - do you want the gap to move too, or do you want to fill the gap to ‘catch up’. We have had similar things and we actually had a “break” activity in the calendar which would stay in place - or move along with changes in the previous calendar items.
The better you plan in advance for everything you want, the better.
The automation takes a little while to trigger (on average about a minute), but you should be able to write your automation in such a way that all following activities (for the same day!) will be processed in one run (use ForEach()).
I am really busy this time of the year, but I can probably help you on your way, once you have figured out exactly what you want. And, like @Christiaan_Huizer pointed out, Coda does not work with previous and next row, but has a much more powerful way of dealing with this, the filter. In this filter you can pinpoint just one row (the one with the next time up), but also all rows with a time greater than the changed row. Even if your table is not sorted, it will still work.