How to shift values down in a row?

[Sonia_Machin]

Hello.

I have a table for spaced repetition. On the photo I have put all the columns manually to show how it should look like.
The thing is that the r1 should take values from r0 and shift it one row down . r2 should make the shift to three rows down. r3 to seven rows. But I never found a function that would change the row position for a value.
As I remember there is an OFFSET function in Excel for this. But do we have anything similar here in Coda?
Thank you.

[Aurelie_PUREUR2]

Hi Sonia !

I don’t know the use case here and I don’t know the OFFSET function in Excel but to do the result you are showing you can do it with a simple Rowid(). It works only if you don’t have to move the order of the rows but since the Name is a date, I think it can match your need. Let me know

[Aurelie_PUREUR2]

A simple version of the formula so you can understand what you are doing.

[Aurelie_PUREUR2]

And the version to remove the symbols

You will have to duplicate the column with formula and replace the “1” or the “4” by the number of rows you want to go down.

[Pch]

Hi @Aurelie_PUREUR2 and Welcome to the Community !

I’m sorry but I think I need to make a side note regarding the use of RowId() in this case :

RowId() returns the id of row which is unique across a table .

It’s never attributed twice in case a row is inadvertently deleted (and/or you would need to remember to undo the deletion of the row as soon as it happened if you want to get it back) and its increment reflects the order the rows were created in the table (meaning that, if you forget to add a row for reason x, y, z… and add it later, the RowId() of this forgotten row will still be bigger than the last row in the table)

In other words, using RowId() will work fine as long as no mistakes are made …

What could be more flexible and less error-prone would be to use Find() to determine the visual position of a row within the table as it will be re-calculated if or when needed :

thisTable.Sort(true,thisTable.Date).Find(thisRow)

This finds the visual position of thisRow in the table sorted by dates

Rank() would also be another possibility :

Table.Date.Rank(thisRow.Date,true)

This ranks thisRow.Date within the list of whole the dates in the table (Table.Date) taken in an ascending order (as the ascending parameter Rank() takes is set to true)

Here’s a sample to illustrate the use of RowId() in a messy table from the start vs. using Find() or Rank() to get to the same result …

[Aurelie_PUREUR2]

Yes, that’s why I said “as long as you don’t have to move the rows” because the RowID won’t change if you just change the order manually and your visual effect of space of 1 or 4 or 7 will be related to the RowID and not to the visual position in the table.

And that’s also why I said that it will not be a problem in a use case with dates because with dates no error possible, “space of 4” in an ordered list of dates is like “space of 4 days” so it will always work.

But in another use case where the source is not an incremented list of dates, I agree with you, it’s not the better solution.

[Aurelie_PUREUR2]

Maybe I need to explain how I create my lists of dates so you can understand why it’s impossible to have mistakes in my method.

Since the use case is a calendar with no need to write by hand the dates I usually use a formula to put the dates. I choose a start date, let’s say 31/12/2022 and then I use the formula Date = Start date + RowID. Then I just make it impossible to delete rows and I have my unlimited autogenerated calendar. Since I use RowId to create the dates, using RowId to space in the calendar will always work

[Pch]

I didn’t say you were wrong … Your method works …

I just mentioned that relying on RowId() in this case, when there are other possibilities, might be a little bit problematic …

I mean, you have your own way to create dates which is perfectly fine … but not everybody stumbling on this topic, trying to re-create something similar, will know about the tiny quirk of RowId() or create dates in the same way with the same cautiousness and that’s when problems might arise …