Formula changes without showing in Activity Log?


I have this formula:

=IF(ISBLANK(Location@row), "", INDEX({Location_code_helper Range 1}, MATCH(Location@row, {Location_code_helper Range 2}, 0)) + "-" + RIGHT(Year@row, 2) + "-" + COUNTIFS(Location$1:Location@row, Location@row, Year$1:Year@row, Year@row))

It basically creates a custom identifier that uses the year and a few other cells in the row, unfortunately this cannot be a column formula because counting functions reference row numbers. Today I noticed a bunch of duplicate ids and found the end of the formula had been changed to -

COUNTIFS(Location$772:Location@row, Location@row, Year$772:Year@row, Year@row))

for all rows. There is nothing strange or interesting about row 772 and the activity log does not show this change occurred so I'm guessing it was something automatic? We have seen some strange things in this sheet due to multiple users sorting but I can't think of why that would change a formula for all rows when its not even a clolumn formula. First question is what/why did this happen? Second, can anyone think of a way to make this a column formula but retain the unique ID across locations? Unfortunately I am stuck using an id that requires counting based on the location cell in each row.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!