Automate data into a cell from another sheet if criteria is met, then remove that data from source

Pam D

Hello,

I have a predetermined list of voucher numbers for exams. When a student meets certain criteria I want a voucher number from the source sheet to generate on the student's row automatically. After that voucher number has been used, it will need to be deleted from the source sheet. The voucher numbers come from the vendor. The student has to be "Approved". The criteria also will be to match the student ID and the exam type. I have sheet A - student information, exam type requesting, manager approval. Sheet B - list of voucher numbers to distribute. There are several different exams and voucher numbers, that will also have to match the exam type.

Is this possible in Smartsheet? Thank you for any assistance!!

Student sheet:

Voucher source sheet:

Vojtech Gajdos

Hi @Pam D

I gave to your question more thoughts and set up some test sheets to demonstrate how could this be working. If you send me your email address I can share these with you.

Regards

Vojtech

Vojtech Gajdos

Hi @Pam D

I might help with the question, just that am a bit confused about the scenario you described:

1. I Couldn't match your screenshot with the sheets you mentioned - is the first one sheet A (in which case - there should be a column with the student details, I guess missing in your screenshot). Or - is the first screenshot sheet B (might be the case as there is a "Voucher Number" column)
2. What is the second screenshot? It only shows 'EXAM 1' and 'EXAM 2' but it is not clear what these columns are
3. I understand that - once (in the sheet A) on a specific row certain criteria are meet (what are these?) than the (next available?) voucher will be put into that student's raw (possibly the column 'Voucher Number' in your first screenshot)?
4. Subsequently, the row with voucher just used has to be deleted
5. The bit 'The criteria also will be to match the student ID and the exam type' is not very clear (guessing your second screenshot is somehow used in this?)
6. It would be good to also describe your sheet with the voucher list

Generally, the above seems to be achievable, just send more info and I can try to help.

Regards

Vojtech

Vojtech Gajdos

Hi @Pam D

I gave to your question more thoughts and set up some test sheets to demonstrate how could this be working. If you send me your email address I can share these with you.

Regards

Vojtech

Pam D

Hi @Vojtech Gajdos Thank you for looking at my issue! I'm sorry my screenshots were so vague.

"I Couldn't match your screenshot with the sheets you mentioned" The first sheet is sheet A - contains student information not shown in screen shot. Their ID, practice test scores, test code they are requesting voucher for, etc... The system involves the student filling out a form, then moves to an instructor to approve - the instructor has to enter the correct student id, and practice test scores before it moves to manager to approve. Once the manager approves, the "voucher ID #" will be automatically input into the Voucher Number column in sheet A, and then more notifications will follow.

"What is the second screenshot? It only shows 'EXAM 1' and 'EXAM 2' " The second screen shot is Sheet B, there are 5 different types of exams the student can request a voucher for. Each Column Exam 1, Exam 2, etc... will contain the list of voucher IDs to pull from. These voucher IDs come from the vendor.

"I understand that - once (in the sheet A) on a specific row certain criteria are meet (what are these?)" The criteria to meet to trigger the voucher ID is simply Manager approval must be "Approved". There are several criteria to meet earlier in the process. This is the only one I am having problems with.

"than the (next available?) voucher will be put into that student's raw (possibly the column 'Voucher Number' in your first screenshot)?" Yes!

"Subsequently, the row with voucher just used has to be deleted" Yes!

"The criteria also will be to match the student ID and the exam type' is not very clear" I apologize! The Student ID is on Sheet A and because there are 5 exams to chose from, test code for voucher requesting, also has to meet criteria. Column 1 has learner ID, Column 7 has test code for voucher requesting. The voucher list (sheet B) has to pull from the correct column, the column that would match Column 7 on sheet A. Sheet A has a total of 24 columns, 3 being helper columns for functions that happen prior to the Manager approving

"It would be good to also describe your sheet with the voucher list"

Sheet B (voucher list) has 5 columns. Each test code. Currently that is all I have on the sheet with the voucher ids listed.

Thank you again!!

Vojtech Gajdos

Hi @Pam D, as I mentioned earlier - I have some demonstration sheets ready to showcase your scenario, pls let me know your email address so that I can share it with you (it's pretty hard to describe the solution with words - better to use the example).

Regards

Vojtech

Pam D

Thank you @Vojtech Gajdos my email is pdriskell@persholas.org. I look forward to seeing what you have created.

Vojtech Gajdos

Hi @Pam D I tried to send an email but the address doesn't seem to work:

Pam D

My apologies! I had a typo, it is pdriskell@perscholas.org

Pam D

@Vojtech Gajdos I forgot to tag you on my previous comment, I'm not sure you received the message. I had left out a letter in my email. pdriskell@perscholas.org Thanks again.