Use Row@row to reference the row above in a column formula

Julie Nelson

I'm attempting to use Row@row along with an Auto Number column, in a column formula, so I don't have to manually update the formula when rows move within the sheet, but am not having any luck. The formula in the Est. RU Date column is RU Date, if it is populated, otherwise it should take the date in the Est. RD Date column in the row directly above it and add 1 day to it (so the Est. RU Date should be one day after the Est. RD Date in the row directly above it). For example (looking at the snipit below), in Row 2, 04/19/24 in Est. RU Date is one day after Est. RD Date in Row 1, 4/18/24.

The formula I currently have to manually adjust when rows move within the sheet (and they move daily) is =IF([RU Date]@row <> "", [RU Date]@row, [Est. RD Date]2 + 1). I am trying to incorporate the Auto Number and Row columns into the formula, based on what I have found it other articles, but no matter what I try, it results in #Unparseable. The new (#unparseable) formula I'm trying to manipulate is:

I really appreciate any suggestions!

Kelly Moore

Hey @Julie Nelson

Try this

=IF(ISDATE([RU Date]@row), [RU Date]@row, IFERROR(INDEX([Est. RD Date]:[Est. RD Date], MATCH(Row@row - 1, Row:Row, 0)) + 1, ""))

Will it work for you?

Kelly

Julie Nelson

Hi @Kelly Moore, your solution also results in #CIRCULAR REFERENCE.

Julie Nelson

@Kelly Moore The formula in "Est. RD Date" was referencing "Est RU Date", which resulted in the #CIRCULAR REFERENCE, so I created a small workaround and now your formula works great! I created another column, "Est. RU Date (DM)" that datameshes "Est RU Date" into "Est. RU Date (DM)". Unless you can think of a better solution, I'm perfectly happy with this solution! Thank you for your help!