row sorting is juggling formula

scott.barrett

We have created a sum function in a row that has been copied down for 100 rows.  We are only reading data from each row......not holding a cell from a previous row.  When sorting and filtering throughout the day, we are finding that the sum functions are trying to read previously held row designations.  Row 10 may read the sum of row 20, and so on.  We have tried using the $symbol to hold the row, and we have tried not using the $symbol. 

 

How should this formula be built to follow the row when being sorted or moved up and down in a sheet?

Shaine Greenwood

Hi Scott,

There's a new @row parameter that you can use instead of the row number in your cell references to ensure that the formula continually references its own row. This also may improve load times and performance a bit. Here's an example of how it works:

=IF([Column A]@row = 25, "Complete")

This is a quick example (so the formula has little practical use). Details on @row are available in our help center: https://help.smartsheet.com/articles/2476491#row

The @row parameter should only be used when you want your formulas to consistently reference cells on the same row that the formula is in.

scott.barrett

Thank you Shaine.  I have not attempted that yet, but that sounds exactly like what we need.

 

Scott

J. Craig Williams

Wait a minute.

When did @row get released? I do not believe it is any of the release notes. 

Craig

scott.barrett

We used the @row function on our formulas.  This worked, and everything held for a few days.  For some reason though, we went back into the formulas yesterday, and they had all reverted back to showing the row # instead of the @row.  I don't know if this is a glitch or why it would revert back, but this cause some big problems with the formulas.  Any ideas why it would revert back on 200 different rows without anyone changing them?

 

Scott

Shaine Greenwood

Hi Scott,

This can happen if someone had the sheet open before you made the change, didn't refresh, and then saved their work over yours. (This would override your changes with the prior state.)

If you're on a Business/Enterprise plan, you may be able to check the activity log to see who had the sheet open before you made your changes. More on activity log can be found in the help center: https://help.smartsheet.com/articles/2476206-track-sheet-changes-with-activity-log

One way to combat this is to make sure that everyone refreshes the sheet often before they make changes, and make sure to have them close the sheet when they're not actively using it.

Preston

Hey Craig,

 

Hope you're well.

I'm with you. When did this happen, and is there any other info than this?

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

This @row feature would have totally prevented 2 separate occasions where all the row references for formulas in my 2000+ row sheet were skewed off by a row. That was fun. Still no idea how it happened. The only thing I can think is someone saved and entered a row via Forms at precisely the same time.