Random discontinuation of formulas in sheets

Marcin P

HI all!

I have a several sheets with formulas. Normal, as almost every user of Smartsheet. I wonder if anyone else has the same issues with formulas as me.

Sometimes, for long time my formulas are copied automatically from row to row without any problems, but randomly, without any reason system stops doing this and I have no idea why. It does not matter if sheet is fed by forms or by direct work in sheet.

The problem is that if I have any notifications triggered by result of formulas, then it doesn't work at all, so I have to keep an eye on it by creating workaround like another formula that checks if the number of rows is equal to the number of triggers (formulas in all rows) and if not, I have a notification rule to be sent to bring my attention and copy formulas by hand, which is nonsense if the number of sheets will grow.

J. Craig Williams

There are several flaws in the Auto-Fill design, in my opinion. Its fragility is the first clue I'm not far wrong.

In my experience, the primary reason for the failure of the formulas to autofill is:

- user overwrites the formula

If the users are all Editors or Viewers, then locked the columns containing the formulas. That eliminates more than half of the problems.

The Sheet may need to be redesigned to account for this.

I hope this helps.

Craig

Marcin P

Thanks for reply, Craig!

One of my sheets has two columns containing formulas, where both of them are locked, so users cannot change or overwrite these formulas and it is still breaking.

 

J. Craig Williams

Do users add rows other than via form?

Does every cell in the formula column have the same formula (no blanks)?

Craig

Mike Wilday

I would check into whether or not users added rows. I have seen the auto-fill option fail when a new row was created and it interrupted the auto-fill feature. Sometimes new - blank rows get added to the bottom of the sheet, besides the ones that Smartsheet automatically seems to think we need, and that can also interfere with the auto-fill feature. 

Marcin P

@Craig, Mike - They add rows by hand. I need to check if they doesn't skip first empty row accidentally and then go back to the provious one to fill it up. Thanks for that suggestions.

Marcin.

Mike Wilday

You're welcome. I hope you figure it out. You might consider having them add rows via a form instead. A form would give you control over where the rows are being entered and ensure that the data that needs to be auto-filled gets auto-filled. 

https://help.smartsheet.com/videos/using-forms-smartsheet

https://help.smartsheet.com/articles/522221-collect-information-form