Formula to check cell history

Michael Culley

I know there's no formula that can check the cell history, but I know there's a work around for what I want to accomplish. I'm just struggling to execute it.

Essentially I have an automation where whenever a cell is changed in the Phone Number Column, I get an automated email. However, I'd like there to be a column that checks whether or not that same phone number was already assigned to that person, and if so, I'd like to not get the automated email.

For example: Joe Smith's phone number was 555-6666. One week later, his name was deleted off the sheet, and moved further down the sheet. His phone number was entered as 555-6666 again. How can I make a helper column to check that he was already assigned that number and to not flag it as a change?

I believe the way to do this is through copying a row to another sheet then using an index match formula to populate a helper column but I feel like I'm missing something or doing it in a sloppy way.

Does anyone have a more sophisticated way of achieving this? Hope my question makes sense.

Adam Murphy

Hi Michael,

I would do it how you described (not sure there is a better way), by copying each row to a helper sheet. The helper sheet would then have an entry for every phone number. On my main sheet I would use a checkbox helper column with an IF statement that checked the helper sheet for a match using countifs. Something like =IF(COUNTIFS({Name}, Name@row, {Phone}, Phone@row) > 0, 0, 1) So if the entry exists in the helper sheet the value will be 0 (unchecked) and if it is not found it will be 1 (checked). Notification automation would add a condition for helper is checked. Would something like that work?