Tricky Mailmerge

[Jawloms]

I have a lady who wants to print around 400 certificates. There are three certificates saved as Word documents, Bronze, Silver and Gold, all with a bit of text in the appropriate colour. She has a spreadsheet with multiple worksheets, one for each class. On each sheet is a list of names and then how many awards they got, which then means whether they get a Bronze, Silver or Gold award. Something like the below;

 

Name                          Bronze    Silver   Gold
Johnny Briggs                  21
Joe Bloggs                               33
Clint Eastwood                                   44

 

So as you can see, the number of awards they have got is in the column representing their certificate colour.

 

She has all the data, but I can't see a way to mailmerge it. Am I right?

 

Thank you

 

Stuart

[clareq]

You will need to use mailmerge filtering - assuming Office 2013, go to edit recipient list and select filter. Choose the bronze field, is not blank and merge to Bronze template. Repeat for Silver and Gold.

[Jawloms]

But how do I get it to look at multiple worksheets in one excel file?

[dapaulio]

I don't think you can mailmerge off multiple tabs.

 

I would merge all the data in to one sheet then create a lookup value which distinguishes which certificate they are issued based on the score range they are in.

 

Then as clareq said filter the recipient list based on the which certificate you are printing. You can even filter by class also if you want to collate by class

[Jawloms]

That's along the lines I was thinking. Previously they have just manually done these things one at a time. I am going to sit with them and come up with a better way of doing it for next year.

 

Thank you both.

[dapaulio]

That's along the lines I was thinking. Previously they have just manually done these things one at a time. I am going to sit with them and come up with a better way of doing it for next year.

 

Thank you both.

I will also talk to the person who does it here and see if I can steal the mailmerge (like as if I need permission I helped design the formulas for it) and email it to you.

pm me your email address