Reference Cell by Relative Location in a Column Formula

I need a means in formula, that would be used as a column formula, to reference a cell using its relative an "@row-1". Is there a way to do this in Smartsheet?


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @RKLRpp

    Yes, one can build column formulas to reference row -1. Typically this will take at least one helper column to do - you may already have it included in your sheet. The COLLECT function will be part this solution.

    In the sheet you are using, are your rows added in chronological order (adding to the bottom?) and are the rows are ever slid up or down the sheet to a new position?

    Is it possible to share a screenshot (remove sensitive info)? This will help me help you build your specific formula.


  • Kelly- tried a solution that mostly works...have an "Auto" column that just numbers rows, and "Row" function column that has the formula =MATCH(Auto@row, Auto:Auto, 0). This "Row" helper column seems to count positionally even if I insert a new row and the "Auto" column numbers chronologically by when row was introduced to the sheet.

    With those I am able to reference with Row@row-1 or Row@row+1 (but only in some function types, I was successful with INDEX) .

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!