Referencing a predecessor row in a formula

Paul J. Puzzanghera

I want to compare the column value in a current row to the column value in a prior row. I want to write a formula that is the equivalent of:

if (row() = 1,1,if(row()=row(-1),2,3)

The @row formulation doesn't take an argument and there is no equivalent to the ROW() function n Excel. The PARENT, CHILDREN functions do this for a hierarchy but all I want to do is reference the current row number and the prior row number.

Relative references should work: if(data2=data1,2,3) but when I sort my data the row references are changed to reflect their new sort position and no longer just reference the previous row.

This seems so basic I'm sure I'm missing something.

Thanks for any help

Jeff Reisman

@Paul J. Puzzanghera

How about using an auto-number system column to generate numeric ids for each row. (Use numbers only with no leading zeros, so that they generate as numeric values as opposed to text.) Use a helper column with INDEX/MATCH to pull the predecessor row data value into the current row, and then perform your IF/THEN against that value.

HelperColumn:

=INDEX(Data:Data, MATCH(([Auto-Number Column]@row -1), [Auto-Number Column]:[Auto-number Column], 0))

IF formula:

=IF(Data@row = 1, 1, IF(Data@row = HelperColumn@row, 2, 3))

Paul J. Puzzanghera

Thank you for the idea. I'm not sure autonumber will do the trick. Once I start sorting the rows the autonumber field will be essentially random. it wont reflect the current predecessor row.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321787#Comment_321787

When a row is first added, the auto-number value is assigned. If the sheet is re-sorted, the auto-number values stay with their original row. So what is the relationship between the rows you are comparing? You need to find some way of maintaining that relationship despite sorting, so think about that relationship and how you might relate them permanently.