Publisher is filling blank lines with data

[Mr. Walsh]

We provide training and distribute wallet sized certificates to students at the successful completion of our courses. In the past, we had just printed out a blank sheet of 9 certificates (3 wide by 3 high) on a sheet of letter sized paper in landscape mode. Then we filled in the certificates with the students information by hand.

I am trying to use Excel to export the students data (name, badge # and course expiry date) into the Publisher certificates. The class size can vary, so nine students is ideal as it would fill up an entire letter sized sheet. Normally we don't have that many students in a course, so there would be unfilled certificates. These unfilled certificates can be used by others in the group who want to fill out the certificates manually by hand at a later date.

I would like the unused certificates to be blank for the student name, badge # and expiry date. The default date setting for Publisher is mm/dd/yyyyy. When I print out a class list of less than 9 students the extra certificates are blank in the name, badge # and date field. When I format the date to mmmm-dd-yyyy; the date field on the blank certificates gets filled in by default. This means that the blank certificates that get printed are only useful on the day of printing, after that, the date for training is incorrect.

Is there any way that I can use the mmmm-dd-yyyy format and have the unused certificates without the date printed out in the date field? I have uploaded the blank sheet that can be filled out by hand. Next is the single template that has the 3 merge fields with my data. The next one is the sheet of certificates with the blanks on the unfilled data fields. The next certificate, I have formatted the date to the mmmm-dd-yyyy. The last image is the result with the unused certificates that have the dates filled in. I have tried to put #;-#;"";"" and 0;-0;;@ into the blank data areas in excel where I am getting the data from, but the date field in the blank certificates still has the date in it.


Thank You
Tracy Walsh

 

[Mr. Walsh]

I have found a solution to this problem. Prior to me figuring out a solution; my Publisher document looked at the first 3 Excel columns that I placed the data in (Student name=A2, Employee ID=B2, certificate expiry date=C2. I used mail merge to import that data into my 3 blank Publisher fields. Sometimes I would get zeros showing up on my certificates (employee ID) even though the cells they were looking at was blank. I was formatting the date field in Publisher document with the mmmm-dd-yyyy format and that is when the dates were showing up in the certificates - even the blank ones that did not have a student name. If I left the date field as the default mm-dd-yy, then the field would be blank if the rest of the data fields were blank, but the date field with student name data in A2 would also have that same format eg 02-20-18 instead of the February-20-2018 that I wanted.

In my data sheet (spreadsheet) I created 3 more columns that refer to the original data columns. In the excel data fields that publisher looks at I have a formula in F2 =IF(ISBLANK(A2),"",A2). (A2 is the student name). I also have the date field (H2) looking to see if there is data in the student name cell, if there is a name there, then the expiry date is inserted, otherwise the date field in the Publisher document is blank. =IF(ISBLANK(A2),"",C2). The same formula applies for the employee ID. =IF(ISBLANK(A2),"",B2)

Now I can have one student in for a course but still print out a full sheet of certificates with one students name filled in and the rest have the 3 blank data fields (Name, employee ID and expiry date) that can be filled in by hand later on.

 

[Becky]

Thanks for sharing your solution