row sorting is juggling formula

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?

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…