Formula Referencing Another Sheet Generating Errors

Hello,

I have a sheet that is returning data from a form and I created another sheet for automated reminders if the form update is not returned. Both sheets have column titled "Email" that is a contact list.

I'm trying to create a formula that references the form sheet Email column and the Email column in the other sheet. If there is a match, to return "Yes" in a column that is driving my automations.

I have used IF, and IF with CONTAINS, HAS, MATCH, and all of them come back #Unparsable, #Invalid Operation, or #Invalid Reference.

I started simply with =IF({Form Sheet Email Column}=[Email],"Yes") and also =IF({Form Sheet Email Column}=Email1:Email7,"Yes").

Thank you for any assistance you can provide.

Best Answer

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @JoAnneCO


    If you have no other references to use and just check if the email exists in the form sheet, you can try this formula.


    =IF(HAS({Form Sheet Email Column}, [Email]@row), "Yes")


    If the email doesn't exist in the Form sheet, this field will be blank.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @JoAnneCO


    If you have no other references to use and just check if the email exists in the form sheet, you can try this formula.


    =IF(HAS({Form Sheet Email Column}, [Email]@row), "Yes")


    If the email doesn't exist in the Form sheet, this field will be blank.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Thank you so much Aravind! I had tried the HAS with [Email]@row before as well, but I was using "=" instead of ",". It is now working perfectly.

    I appreciate your assistance!

    JoAnne

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!