Can I repeat data from one record on all records in a mail merge?

January 27, 2011 1:46 PM

Looking for a word/excel mail merge trick that may not exist. Basically, what I'm looking to do is put information from the header row itself into the merge document so that it appears in the merge for each other record.

In other words, row 1 in Excel (which I'm using for the data source for my merge) would have the the criteria that I am evaluating a project on (e.g. content, spelling, sources used, etc) and rows 2 through 25 would show my students' names and their scores for each category. If I were only doing this once or twice, the obvious solution is to just type the criteria directly into the merge document. But I want to be able to do multiple merges each day without having to create new merge documents. I'm using excel and word 2003. Any thoughts?

posted by robverb to computers & internet (4 answers total)

I'm sure there's a fancier way to do this, but what I'd do is just intersperse columns with the criteria headers in them. Column A would have the word "name" in A1 and then the students names in A2-An. Column B would have the word "spelling" in B1-Bn. Column C would have "Spelling Score" in C1 and then the students scores in C2-Cn. And so forth. Then, just plug in those column values to your merge documents where you want the criteria names to appear.
posted by decathecting at 2:15 PM on January 27, 2011

I don't know of any way to insert merge header names into the merge document, and I tried a few ideas on my setup (exactly like yours) and came up blank each time. decathecting's method would probably work best for exactly what you are asking.

I have a question, though. You say "But I want to be able to do multiple merges each day without having to create new merge documents." What is the reason behind having to create new merge documents each time? Do you have to make a completely new structure for each document every time?

Could you set up the mail merge exactly like you want it - with the criteria name typed into the document, like you said - then save the un-merged document for reuse later? Each time you open that document, it will pull and update the data from the spreadsheet you linked it to when you first created it. You can also use the "change merge source" dialogue on the mail merge toolbar to switch to data stored on a different spreadsheet (or a different tab) if the field names are the same on all of them. Or could you save a couple of un-merged documents for the various types of mail merges you do, then reuse the appropriate one each time?
posted by gemmy at 4:44 PM on January 27, 2011

Hoping this helps... I made two files, source.xls and merge.doc, so you can examine them for reference. (Save both files locally; the first time you open the Word doc you may have to point it to the location of the Excel file.)

Create your data in Excel, using headers at the top of each column. Save this worksheet.

Begin a Word doc and start the mail merge wizard. I chose to lay this out in a table so that fields with varying lengths would still line up properly.

In your mail merge wizard, link to your Excel spreadsheet as a data source, and make sure the “First row of data contains column headers” box is checked. Go to the next step in the merge, where you can add Address Block or other recipient fields. I used “More items…” to manually place fields where I wanted them, in the second row.

To get the first row headers, with the Excel spreadsheet open, I clicked on my first header cell, “Last Name”, hit Copy, and then went into that table cell in Word, and did “Paste as Nested Table”, and chose “Destination Formatting and Link to Excel”. Do this for all remaining header cells.

Then, when a header value is changed in Excel and the sheet is saved, it will dynamically update in Word as well. I made columns for "First Name", "Last Name", "Content", "Spelling", and "Sources". If you changed "Sources" to perhaps "Presentation" and then saved it, it would dynamically update in the Word doc.


When you open the Word doc at a later date, answer “Yes” to both prompts regarding updates, to make sure you have the latest info from your excel sheet.
posted by xedrik at 9:44 AM on January 28, 2011

@decathecting - that is a pretty good workaround, although it requires twice as many columns and, believe it or not, I max out the available columns over the course of the marking period (I use Docs to Go on my Droid to do assessments during the day and the limit is much lower than on Excel).

@gemmy - the problem is that for each assessment I do I need different criteria, so I either need to change the word document each time or have the word document be able to pick up heading data from Excel

@Xedrik - it looks like your idea is the best I can get. I tried it out and it works well. I had hoped to make it more dynamic - i.e. I wanted to be able to open the word document and keep the data source the same, but update it to read from, say columns G, H, I, and J instead of B, C, D, and E. and automatically pull up the heading data. What I've done, building on your idea, is create a second Excel spreadsheet that is linked to my master spreadsheet. I can very quickly change the links to read from different columns as needed so it will reflect whatever headings and data I want. I then linked my Word document to that spreadsheet and, using your trick, it will put the headings right where I need them. Thanks.

Of course, if anyone else reading has a simpler solution, I'm still open.
posted by robverb at 7:25 PM on January 28, 2011