Referencing a predecessor row in a formula
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
Answers

How about using an autonumber 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(([AutoNumber Column]@row 1), [AutoNumber Column]:[Autonumber Column], 0))
IF formula:
=IF(Data@row = 1, 1, IF(Data@row = HelperColumn@row, 2, 3))
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.

When a row is first added, the autonumber value is assigned. If the sheet is resorted, the autonumber 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.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!