Question

I am trying to figure out how to automatically calculate a cell value based on the value in a cell in the row just prior to (above) it, that will re-reference if the rows move. I understand relative and absolute row and cell referencing. I also understand @row.  I would think it would look something like this (I understand that @row will not accept an operator n, I just use that to illustrate)

[Column 1]@row(n) = [Column 2]@row(n-1)

In this context, no matter how I dragged the rows around, the calculation would always reference the row just above it. Is something like this possible in Smartsheet?

Thanks!

David Joyeuse · Accepted Answer

Hi @Robert Mase

You're gonna need an helper column that will replicate row numbers. Than an INDEX function should do the trick.

[Row Number] column:

=COUNTIFS([Main Column]$1:[Main Column]@row, OR(@cell="", @cell<>""))

That is if you have some empty cells in the main column. If not, a simple COUNT function on the Main column will work as well.

Then in your Column 1:

=INDEX([Column 2]:[Column 2], [Column 1]@row -1)

Let me know how this works.

Hope it helped.

Robert Mase · Accepted Answer

That works, Thank you! However, I find that  the values in the Column being evaluated (by INDEX) need to be static or independent. I am attempting to use using calculations (calculate a value in the current row, based on the value in the row prior), and getting circular references. Working my way through that... Thank you for the help!