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 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))
Regards,
Jeff Reisman
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 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.
Regards,
Jeff Reisman
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!