Bug with Modified By?

Liz Polizzi

I've been trying to figure out a way to trigger an automation when a row is "updated" by a specific person. Using the built-in "Modified By" column seems like a great solution -- but when I send an update request for a SINGLE row, and in response to that request that one row gets updated, weird things happen: (1) the Last Modified date doesn't change (weird?), and (2) the Modified By column gets filled in for EVERY row (weirder), and (3) the person added to the Modified By column (for EVERY row) is not even the person who made the update (weirdest!).

Has anyone else encountered this weirdness? I have to say, this sheet I'm working with is rather complex, but these fields should be automatic and shouldn't pull from any of the other complex parts of the sheet.

Any advice would be welcome!

Liz

Paul Newcome

Are any formulas using the TODAY function? Are there any other automations on the sheet? Do you have any formulas referencing entire columns to output a count or sum (or any other output based on an "overall" type of calculation)?

Liz Polizzi

https://community.smartsheet.com/discussion/comment/386853#Comment_386853

Thanks for your questions! Yes to everything except formulas referencing entire columns. I do have a few VLOOKUP formulas that reference a separate sheet. Do you think that might be the problem?

Paul Newcome

It depends on the formulas. This specific example may not be applicable to you, but it shows how a formula could be the cause.

If I want to flag duplicates, I would use a COUNTIFS in a checkbox. Even if the box itself doesn't change (checked vs unchecked), if the COUNTIFS nested in the IF statement changes to a new number on the back-end, it could trigger the Modified column to update.

=IF(COUNTIFS(Column:Column, @cell = Column@row)>1, 1)

If I have two rows that match, then the back-end version of the above is

=IF(2>1, 1)

But then I create a third row that matches and the back-end formula changes to

=IF(3>1, 1)

The checkbox itself hasn't changed because both 2 and 3 are greater than 1, but the underlying data has. This means that each row impacted will have technically been "modified" even if just on the back-end.

The TODAY function in a formula can also create the same issue because the date stored on the back-end in the formula changes every day.

Liz Polizzi

Thank you, @Paul Newcome. The idea of hidden, underlying formulas helped me figure out the one that was at work in my case.

In case this helps anyone else, the issue ended up being that I had a lookup column (I was using VLOOKUP, but even after I changed it to INDEX/MATCH it still happened) that was retrieving email addresses from another sheet and putting them into a column formatted as a contact list. Apparently something about it being a contact list was causing the issue. I solved it by formatting the formula column as text, and then creating a second column that pulled in the values from that column -- and that second column CAN be a contact list without breaking everything.

I still have no idea what was happening behind the scenes that caused the strange behavior, but once I fixed that, it stopped.

Thanks again for your help, Paul!

Paul Newcome

Glad you were able to get it sorted.