I am very new to excel and have had to design a solution for a rewards system. I have completed the majority of the solution but need to transfer the names of the winners and the prize they have won via mail merge onto a certificate.
My problem is, I have set up my system so the possible prizes to win are columns, with the students being rows, and allowing teachers to select "Yes" or "No" for each pupil for each prize.
This means that my data cannot be mail merged as where the name of the prize should be, it will insert "Yes". Furthermore, students who receive "No" for a prize are also merged, with the prize being called "No".
I considered re-starting my entire system but I have lots of work documenting all the formatting, validation, formulae and macros I have used, it would take forever to re-do.
I thought it might be possible to create a new sheet which simply shows the names of pupils who have won prizes and the name of the prize beside their name. The columns would then be "Name | Award". I would also make sure I created a new set of columns for each prize (e.g. sport) to make things simpler for myself.
I have had trouble with this. I used an IF statement that copied the name of a student onto the sheet if, for the specific prize (e.g. Sport), their teacher had selected "Yes". However, I needed an option for if false so I used " ".
This left blank spaces in my merge table which then created blank certificates.
I need to find a way to just get the student name and award name of pupils who have won each prize.
I'm really sorry that this is so long winded by I wanted to make my problem as clear as possible.
Bookmarks