How do I create multiple forms that mail merge from 1 Excel spreadsheet?

I’ve just started a new job at a small niche healthcare company, and one thing that I’ve noticed is that many people at the company have to repeatedly fill in patient information into the same set of five forms. My thought was that everybody could save time by putting the information for the patient and the provider visit into one spreadsheet, then mail merge that into the five separate forms (the one that goes to the employer, one to the doctor, one to the pharmacy, etc.), rather than laboriously find all the spaces to fill in on each form, then retype the patient’s name, clinic address, etc. After noodling around with Microsoft Excel and Word for a while, I can’t figure out how to make a mail merge where the information isn’t put into each document in the order of the spreadsheet’s columns. Is there a way for me to create a mail-merge-like situation in which I could put the patient’s name in a spreadsheet once, but it appears multiple times in different locations on different forms? My personal level of technological competence is that I can paste in somebody else’s macros, but don’t know how to write them; can download drivers for a misbehaving peripheral, but can’t really fix anything beyond that, and so on.

What version of Office? In general you create the spreadsheet, make the Word doc with all the forms, select the spreadsheet as the data source, then insert the merge fields wherever you want and as many times as you want.

In Word 2010 go to the mailings tab, ‘select recipients’, ‘use existing list’, select the spreadsheet, position the cursor, and ‘insert merge field’.

Your biggest problem will be that only one form (Word doc) can access the data (Excel s/sheet) at any given time.

There should be no problem doing this. (I did some this week, 2 form letters & an envelope, all coming from the same spreadsheet. All using the old Office 2003.) And there is no requirement that the fields in the document be in the same order as the columns in the spreadsheet.

The biggest problem may be as grimpixie says, only one person at a time can access the spreadsheet.

A possible solution for this (and maybe more efficient for your small company): make one long document that is all the 5 forms one after the other. In it, have the mail merge fields (name, address, etc.) filled in from the spreadsheet. (Yes, you can repeat them many times in the document.) Then a single person can create this one document from the spreadsheet, and you can print all 5 forms at once, or just print whatever one you want, by printing selected pages from the document. Another advantage of this – since they are all from one data source, you avoid possible data entry errors when 5 different people are each keying the data. No embarrassing cases where the forms on the same patient have different birthdates, or the patients name spelled differently.

I had pretty much the same thought… you simply put all the forms into one workbook, perhaps a form on each page, and the cells with the field data are just set to be equal to the cells where you enter the data.

A side note. I used to work at a hospital, and a few years ago we had to MS Access and Excel to store any patient personal or medical data. Our IS department determined that the programs were not secure enough to store confidential patient information. The were all kinds of rules about data that changed with the passage of HIPPA (a patient privacy law). It’s something you ought to look into because if you store any of this data in Access or Excel, you may be in violation of the law.

Disclaimer: I’m not an IS professional, a legal professional, or a health care professional, so I can’t speak authoritatively about this privacy issue.

Thanks for all the advice!

I’m using Word and Excel 2010, but I don’t know what versions our contractors around the country are using. I’d asked about HIPAA in another context, and was told it didn’t really apply to us, as we’re not actually care providers, but I don’t know if I believe that, and will need to follow up.

Each person could create their own spreadsheet, to be used just for making these forms. There’s no larger use of Excel company-wide to share data, so theoretically they could not save the sheet after the merge was done, avoiding HIPAA concerns. I just think that rather than enter the person’s name in on fourteen places on five sheets, along with a bunch of similar examples (doctor’s address three times, DOB five times, etc.), it might be more efficient if I could enter each fact once on a disposable spreadsheet, then have it mail merge into the forms.

Don’t believe 'em about HIPAA. If you are touching patient information and working in healthcare or for healthcare providers, then HIPAA applies to you.

Using Excel/Word/Access is fine as long as you’re not saving the patient data in those formats, or are saving it to a very secure location. (I’m assuming that your network and computers are properly secured.)

Sounds like you’re getting some good advice here on your technical question, so I’ll leave that to the others.

Good luck with your project. It sounds like an excellent idea.

If it’s for a small number of users check out DataPrompter.