New books this season: A PowerMerge and QueryCell Fictional Case Study (Part II)

This is the continuation of the Case Study showing how to use PowerMerge and QueryCell to automate the creation of personalized presentations using PowerPoint and data contained in Excel.

First of all, I create a template for the presentation that will be sent along with the email. This presentation will contain our common corporate presentation material, things like who we are and contact details, but we want to really focus on the things that really interest our partners:

• The events that are to be held nearby
• The new books on our catalog that interest them

Creating the events information

This year we are going to participate in nine events. The marketing department has already filled in a spreadsheet that contains the most important information about each event, including a picture with the event logo and a plan of the exhibition center, with our booth location clearly marked.

I will present the event information in two slides:

The first slide contains four text variables: Event, Date, Centre and Location which will be substituted with the information contained in the Events workbook. It has also an image field which will be substituted with the logo for each event.

There is also a strange looking field:

[IF|REGION=[REGION]]

This is a little trick. I have a field named Region (note that fields in PowerMerge are case insensitive) on the data sheet. PowerMerge will replace the value of the Region field for each event so that, for example, in the first case, it will be replaced by:

[IF|REGION=North East]

And that is a “conditional” field. On my final merge, only partners in the North East region will receive information about this particular event.

The second event slide contains details about the physical location of our booth on each event:

I have used another little trick of PowerMerge. I want the first and second slides for each event to be “grouped” together. Therefore, on the first slide, I have checked the “Keep with Next” option.

Now, I have to merge my template presentation with the Events data sheet.

PowerMerge has two modes of operation:

• Merge combined: Creates a single presentation repeating all slides that contain variables (columns) for each record (row) in the data sheet.
• Merge multiple: Creates a new presentation for each record in the database, but individual slides are not repeated.

In this case, I need to make a “Combined merge”. After a few seconds, I get my new presentation containing all the information of the nine events:

As you can see, both slides corresponding to each event have been grouped together, just like I wanted.

Remember the conditional field trick I told you before? PowerMerge has made the substitution, as can be seen below:

Creating the books information

Now that the events part has been solved, I will create one slide for each of our new releases. With more than 60 new books this season, doing it manually would take me the better part of one day. With PowerMerge it will take less than a minute.

As before, I have already the books information stored in a workbook, named, Books:

And I have a slide in my template presentation that uses these fields:

This is a piece of cake. As before, I have a number of text fields, an image field to show the cover of each book and I use a similar trick for the conditional field. This time I am doing it a little differently. Each of the libraries can either carry or not each category, so I will have to create a field for each possible category.

It turns out that PowerMerge has two kind of conditional fields. The first type is the one we saw before, where a record has to have a particular value for the slide to be included.

The second kind of conditional field omits the value. When PowerMerge sees a conditional field, like [IF|FIELD], it will check for each record if the value for the field is empty or not. If it is empty, the slide will not be added to the merged presentation.

I have to make another “Combined Merge” to create a slide for each book. The result is shown below:

Wow! I am already loving PowerMerge.