Formula changes without showing in Activity Log?

ricnao

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.

Cory Page

Not super sure why this happened but it might be easier to use the "Auto number" for column properties then simply use =Location@row + AutoNumber@row + however unique you want your string..

This works if you don't really need a count and its pretty easy I use it a bit here and there.

If you still need a count of rows I am sure we can come up with something figured I would check with the above first.

ricnao

Thanks for the suggestion, they want to have the count by location and it'd be ideal for them but at this point its been causing so many different issues I feel I need to push back and go with autonumbering.

After investigating I think someone sorted the sheet and the row that was at 1 ended up at 772 which then caused the formula to change. If you have ideas for a formula that would be resilient to sorting, maybe somehow using the created date column but I cant really think of a good way to do it and I'd ideally want to use a column formula so avoiding any of the count(cell$1:cell@row stuff).

Paul Newcome

I would insert an auto-number column (called "Auto" in this instance) that has no special formatting then adjust your COUNTIFS like so:

COUNTIFS(Location:Location, Location@row, Year:Year, Year@row, Auto:Auto, @cell<= Auto@row)

ricnao

Thanks Paul, what exactly is the @cell referencing?

Paul Newcome

It is basically just telling the COUNTIFS to evaluate the previously established range on a cell by cell basis. Not always needed, but never hurts to include, so I tend to always use it.

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