Comparing Smartsheet User Name (Contact) to Modified By in a formula

Options

Hi ...My solution and struggle is as follows :

I send an automated update request to a contact in a column

When the update request is submitted, I compare the submit date(modify by date) to a due date and allocate points for on time submission.

To validate that the contact person and not smsh or admin made the update, I compare the Contact (email) to the modify by as a validation.

My issue is that where contact is a registered smartsheet user the system doesn't recognize the email address as being the same.


Please help !

Thank you,

Lee

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I would suggest creating a table that houses everyone's names in one column and emails in another. Then on your main sheet you can use a formula to say that if there is no "@" (meaning it is a name) then hit against the table to pull the email address.

    =IF(FIND("@", [Contact Column]@row) = 0, INDEX({Table Sheet Email Address Column}, MATCH([Contact Column]@row, {Table Sheet Name Column}, 0)), [Contact Column]@row)


    Then you can compare this new column to the modified by column to verify.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!