Wednesday, September 16, 2009

Mail Merge and Named Ranges

Okay, so today I was helping out a friend over at Husker Diesel who's pulling together a customers/contacts list into Excel. One of his planned uses is for printing mailing/shipping labels. "No problem," i thought. "Let's just crank up a good old-fashioned mail merge in Word.

Turns out it was pretty easy, but i ran across some knowledge along the way i thought I'd share:

For a variety of reasons that are immaterial to the story, the Excel contacts list we were working with wasn't just a simple top-row-contains-headers type of list. There's a form at the top of the page and the column headers started further down the page -- say row 11 or so. Sooo...when i browsed for the Excel file in the mail merge process what it pulled back wasn't very useful -- the form part of the worksheet was included in the contact records since it's at the top. "No problem," i thought. "Let's just create a dynamic named range that will define just the contact records part of the sheet .

Turns out that mail merge doesn't recognize dynamic named ranges. ?! Odd.

I couldn't find a lot out there on this issue, so I ended up just using the work around of created a "hard defined" named range of 10,000 rows of the contacts list, just to be sure there was ample room for all the contacts he'd be adding. Doing this caused me to have to select only non-blank rows during the mail merge process, but it worked just fine.

If anyone out there has encountered this and has a better work around, I'd love to hear it.

Later,

Excel_Geek

1 comment:

RIRose said...

I've seen the same issue in Access - it would not recognize dynamic ranges. I had to do the same thing - and just hope I included enough rows for future records!