referencing using @row

I've tried multiple searches and haven't found the right keyword/tricky phrase to answer my question.

I am using a form for submissions and trying to calculate how long a trend has been occurring. Right now my working formula is this:

=IF(DidIncidentOccur@row = "Yes", 0, IF(YEARDAY(Date1) > YEARDAY(Date2), DaysSince2 + (Date1 - Date2), DaysSince2))


The problem is that if I have to manually enter a row because my operator forgot to enter data, it breaks the formula and I get a whole slew of "invalid data" or "circular reference" errors. I would like to think there is a way to use the @row function to get something like this:

=IF(DidIncidentOccur@row = "Yes", 0, IF(YEARDAY(Date@row) > YEARDAY(Date@row-1), DaysSince@row-1 + (Date@row - Date@row-1), DaysSince@row-1))


Basically this would mean that anytime the formula is used it is referencing the data in the same column, but 1 row below where the information is entered. Is this even possible in smartsheet? I remember being able to do it using the OFFSET formula in excel, but it appears through searches that even after 2+ years of requests for that function it hasn't been built in.

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!