Can I connect an auto-generated number to an email and link to separate sheets?

Options

Our company is building out an event planning workspace.

We have one sheet that tracks the invitees/invitation history, and one sheet that tracks the information collected on the RSVP form for those who respond (via form) to the invitation.

When a person is added to the Invite sheet, there is a Guest ID generated for them.

We would like the Guest ID from the invitation sheet to be tied to their email so when a person RSVPs, their Guest ID will auto-populate on the RSVP sheet.

Is that possible? Is there a different/better way to have the same Guest ID from the invite sheet populate on the RSVP sheet?

Thanks for any help you can offer!

Answers

  • Frank B.
    Frank B. ✭✭✭✭✭
    Options

    Yes, it is possible to have the Guest ID from the Invite sheet auto-populate on the RSVP sheet when a person responds to the invitation.

    One way to achieve this is to use the VLOOKUP function in Smartsheet. You can create a formula in the RSVP sheet that looks up the Guest ID for the email address entered on the RSVP form, based on the data in the Invite sheet. Here are the steps:

    1. In the Invite sheet, make sure the Guest ID and email address columns are adjacent to each other.
    2. In the RSVP sheet, create a column for the Guest ID and enter the VLOOKUP formula. The formula should look something like this:

    =VLOOKUP([Email Address], [Invite Sheet Range], 2, FALSE)

    • Replace [Email Address] with the cell reference for the email address entered on the RSVP form.
    • Replace [Invite Sheet Range] with the range of cells in the Invite sheet that includes the Guest ID and email address columns.
    • The "2" in the formula tells Smartsheet to return the value in the second column of the range, which is the Guest ID column.
    1. Drag the formula down the column so it applies to all RSVP responses.

    This will allow the Guest ID to auto-populate in the RSVP sheet based on the email address entered on the RSVP form, as long as the email address matches one in the Invite sheet. If the email address is not found in the Invite sheet, the formula will return an error.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!