Index Match contact list and automated emails problem

Raul Cabrera

Hi good morning. I have a issue with one sheet that i created and specific with a column that return me an email with an index match formula. What we try to have is a contact person if we choose the Director or that business. i.e. Director is John Doe and the formula return in other column Lead email Hirushiyamamoto@test.com, this from another sheet where i create how to map. All good with this and running perfectly. After that i created an automation flow in order that every new cell or change where the lead and owners are, they have to be notified. Both columns are type "contact list". The problem arrives when the automation detect changes in the each cell and it seems that SmartSheet is validating the Index Match formula every X time and as it is detecting changes in the Lead column, send an email with the change. I tested using the Highlight changes tool and appears that the Lead column is changing frequently even when nobody is doing nothing. See attached.

If i change the column type to "Text" the changes stop to show, but i can not automated the flow sending emails because the column type is not a contact list.

Any idea why Smartsheet is validating this formula (Index/Match)?

=IFERROR(INDEX({Delegate}, MATCH(Owners@row, {Owner}, 0)), "")

This index match was created as i mention because we have an intake form where we just request the director name and automatically we assign the lead without have to ask the lead in the intake form. What i don't want is to request the lead email in the form.

Genevieve P.

Hi @Raul Cabrera

Can you set your automation to send out only if specific columns are changed/updated? So instead of saying if anything changes, you could say if anything in these columns changes, and exclude the formula column:

The cross-sheet formula will continually check the other sheet to see if there have been updates made to ensure it's bringing through the correct contact on each save/open of the sheet. This means that even though the value hasn't changed, the formula "updates" or refreshes in order to check for new content.

An alternative to the INDEX(MATCH would be to use the Assign People workflow, but that would only be a good solution if you had around 10 possible contacts to assign, and it wouldn't be updated frequently.

I hope that helped!

Cheers,

Genevieve

Genevieve P.

Hi @Raul Cabrera

Can you set your automation to send out only if specific columns are changed/updated? So instead of saying if anything changes, you could say if anything in these columns changes, and exclude the formula column:

The cross-sheet formula will continually check the other sheet to see if there have been updates made to ensure it's bringing through the correct contact on each save/open of the sheet. This means that even though the value hasn't changed, the formula "updates" or refreshes in order to check for new content.

An alternative to the INDEX(MATCH would be to use the Assign People workflow, but that would only be a good solution if you had around 10 possible contacts to assign, and it wouldn't be updated frequently.

I hope that helped!

Cheers,

Genevieve