How to automatically # rows with a formula?

Alex Fullerton

I had a column which uses a formula referencing the value of the row below it and then add one. For example the row 2 value is [Opp Num]3 + 1. This formula automatically carried to any new entries for months allowing for continuous unique numbering of new rows. Recently this formula has not been working as intended as it is not changing the row reference or not populating all together. Does anybody know how to fix this issue or create another solution? I do not want to use auto numbering because I still need to allow for manual changes.

Jeff Reisman

@Alex Fullerton

If I recall correctly, Smartsheet will carry formulas (and advance row numbers within them) down if the two rows before the new row both contain the formula.

If you manually remove the formula in either of the last two rows (by writing over the value,) you break the chain. You'll have to set the formula manually in that new row, plus the next one, in order for the formula to keep incrementing automatically.

Now, there's a way to avoid this, if you have room for a helper column. Create a column "Manual Opp Num". Change you formula in Opp Num to be:

=IF(ISBLANK([Manual Opp Num]@row), [Opp Num]914 + 1, [Manual Opp Num]@row)

Then, if you want to override the Opp Num value, put that value in the Manual Opp Num column and it will replace the generated value with the manual one, without overwriting the formula.

So if you have

914

915

and then set the Manual Opp Num on the next row to 917, the next row after that will automatically be generated with Opp Num = 918.

Alex Fullerton

@Jeff Reisman The formula was carrying and advancing row numbers without issue until recently. I can confirm that the rows below the new row have this formula. On the most recent submission it seems to have worked at first and the was overwritten by Smartsheet Automation. Any idea why this would be happening?

Jeff Reisman

@Alex Fullerton Is there a formula in that cell you're showing the history of? If so, what is it?

Is there any other automation running on this sheet?

It says that a Form User set the value to 917 first. It's a very strange practice to use manual entries on formula fields, for this very reason. That's why I prefer to use column formulas, and if manual changes need to be possible, to make the mechanism for that part of the formula.

Alex Fullerton

@Jeff Reisman The formula in the cell is [Opp Num]3 + 1 carried from the cell below. There are plenty of automations running on this sheet but none that would affect this cell value as far as I can tell. The strange thing is that even though it says a form user set the value to 917, we actually don't allow form users to enter a value in this column as it is meant to be a formula carried from the rows below.

This was running without issue for hundreds of entries so I wish I could figure out what the issue is now, otherwise I may need to come up with a different process.