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:
- User submits a form to request a voucher for a specific exam. Row in SmartSheet 1 is populated with user's data.
- 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.
- 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).
- SmartSheet pulls in that voucher code to the Voucher Code row in SmartSheet 1 that corresponds with the user's form submission.
- 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).
- 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
Check out the Formula Handbook template!