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

LauraPS

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!

Thanks,

Laura

LauraPS

This is the form

Paul Newcome

The problem you are going to run into with this setup is a circular reference issue.

What you will need to do is make sure your voucher code list is in the order you want it to go in grouped by exam.

Next you will insert an auto-number column with no special formatting into the form sheet.

Then you would use something like this:

=INDEX(COLLECT({Voucher Codes}, {Exams}, @cell = Exam@row), COUNTIFS(Exam:Exam, @cell = Exam@row, Auto:Auto, @cell<= Auto@row))