Can I import new data to a separate sheet without a reference?

I have a Zap set up to add a new row to a Smartsheet when a student completes a course. I want to assign the student a voucher code from a list on a separate sheet, and update the Student info row with the voucher code.

The index match column formula retrieves the voucher code from the other sheet when the email address match, and I have another Zap set up that will send an email containing the voucher code when a row is updated.

I'm stuck on how to build a formula that will assign the voucher code to each student's email when a new row is added. I've tried cell linking, but this breaks the zap, since it is designed to add a new row, which loads below the linked cells (if I have 250 cells linked between sheets, the zap will add the new row at 251).

Any advice or alternate solutions would be really appreciated. Thanks Smartsheet community!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @SIFF_Volunteers

    What if you set up an Auto-Number column with the exact same numbering system in both sheets? (See: Auto-Number Rows)

    For your sheet with the codes this would be pre-populated:

    But then in your intake sheet, this Auto Number column would only provide this row ID when a new row is created. That way you can match across sheets using the Auto Number value instead of the email:

    Does this make sense / do you think it will work for you? Let me know if I've misunderstood your current set-up!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!