Reference Cell by Relative Location in a Column Formula

RKLRpp

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

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

RKLRpp

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) .