Pulling in selected data from another sheet when conditions are met & then marking that as used


I have a complicated use case I could use some help with. Apologies if I'm using the wrong words here; happy to provide clarification. Here's what I'd like to happen:

  1. User submits a form to request a voucher for a specific exam. Row in SmartSheet 1 is populated with user's data.
  2. Upon row population, another sheet (SmartSheet 2) is accessed that has a list of unique voucher codes, specific to each exam (e.g., Exam 1 has 50 unique codes, Exam 2 has 50 unique codes). Let's say the user requests a voucher code for Exam 1.
  3. SmartSheet goes to SmartSheet 2, finds the list of voucher codes for Exam 1, and then finds the first one that is isn't marked as Used (whether that's a checkbox, or a "Yes" in a used column-- whatever will work).
  4. SmartSheet pulls in that voucher code to the Voucher Code row in SmartSheet 1 that corresponds with the user's form submission.
  5. SmartSheet 2 changes the Used column in the row for that voucher to indicate it's used (whether that's a checkbox, or a "Yes" in a used column-- whatever will work). (I have this set up successfully currently to mark a checkbox as Used when the Voucher Code is populated in SmartSheet 1).
  6. Once the voucher code is populated, an automated email is sent to the submitter (this part I have already created successfully).

So if another user submits the form for a voucher for that same exam, they'd get the next voucher down the list for that exam-- not the one that was already used.

I had a meeting with the pro desk, but they weren't able to solve this for me. I would be so grateful for any help!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!