Scenario
I was asked to set up a mail merge letter for my client. I knew I could create a ‘database’ in Excel and refer to that for the mail merge fields in the Word document. However, some of the fields would have data in them that would be used in every letter, and this data was ideal for a drop-down selection list.
Such selection lists simplify the process for the person entering the data in the Excel spreadsheet, save them time, and make the data entry more accurate as they can’t make spelling errors or typos.
For example, one of the mail merge fields I created was for the type of document that was referred to in the letter; the only acceptable document types were:
- Appendices
- document
- Plan
- Program
- Report
- Supplement
I knew I could create the data lists for the drop-downs in Excel (thanks to a great session on just this by Michael Hughes at the 2009 WritersUA Conference), but I wanted them on their own worksheet, and I wasn’t sure how to reference them from the data entry sheet (I’m an Excel novice!).
Solution
In this tutorial, I’ll show you how to set up a list in Excel 2007 and how to reference it from cells on the main data entry worksheet.
Bottom line: Reference cells in another worksheet by adding the worksheet name plus an exclamation mark between the = sign and the cell range (e.g. =Lists!$A$2:$A$7 for a range of cells on the Lists worksheet).
Step 1: Name the worksheets
- Open a new Excel document.
- Change the name of Sheet1 to MailMergeData — this will be the data entry sheet.
- Change the name of Sheet2 to Lists — this sheet will contain the list items for the various fields.
- Save the document. For this example, call it MailMergeData.xlsx.
Step 2: Name the data columns
- Go to the MailMergeData sheet.
- In Row 1, enter the names of the data fields that you’ll use in your mail merge letter — one in each cell. Don’t use spaces, symbols or punctuation in the names, and don’t duplicate any names — each has to be unique. Try to make the names meaningful and recognizable; these names will be listed in the mail merge field selection list, so you need to be able to figure out what they are. (In the screen shot below, I’ve used RecFirstName and RecLastName for the recipient’s first and last names, RecOrganization for the recipient’s organization, DocType for the type of document.)
- For ease of reading, format Row 1 so that the field names are obvious (e.g. increase the font size, add a background color, make the font bold, etc.).
- Save the document.
Step 3: Set up the list items for the selection lists
- Go to the Lists worksheet.
- In Row 1, Column A, type the name of the data field for that particular list (this name is only for you/someone else to identify at a later date — it isn’t used in the mail merge nor is it critical to the drop-down lists you create; I tend to use the same name as that in the MailMergeData sheet, but this isn’t essential.)
- Move to the next column in Row 1 and type the name of the next data field that will have a drop-down list.
- Repeat step 3 for as many lists as you need to create; remember, not every data field in your mail merge dataset will have a selection list.
- For ease of reading, format Row 1 so that the field names are obvious (e.g. increase the font size, add a background color, make the font bold, etc.).
- Underneath Row 1, Column A, type each list item, one per cell. Try to keep them in alphanumeric order as neither Excel nor Word sorts them automatically.
- Add the list items for the other data fields.
- Save the document.
Step 4: Find and select the list item range
Next you’ll select each list item range to find out their row and column values.
- Go to the Lists worksheet.
- On the Data tab, click the Data Validation button, then select Data Validation. This opens the Data Validation dialog box.
- In the Allow field on the Settings tab, select List.
- Click the icon to the right of the Source field to open a small Data Validation window.
- Select all the items in one of your lists by clicking and dragging your cursor over those cells.
- As you do this, the selection is shown in the Data Validation field as something like =$A$2:$A$7, which indicates everything in column A, rows 2 to 7 inclusive, as shown in the example below.
- Press Enter to show the full Data Validation dialog box. The cell range is displayed in the Source field.
- Select everything in the Source field and copy your selection to the clipboard.
- Click Cancel as you don’t need this dialog box again for this list.
Step 5: Add the list item range to the mail merge field
In this step, we’ll select the field in the MailMergeData worksheet and use data validation to link to the other worksheet’s item list.
- Go to the MailMergeData worksheet.
- Select the first empty cell in the column where you want the drop-down list to go. In the example below, I selected the first cell under the DocType heading.
- On the Data tab, click the Data Validation button, then select Data Validation. This opens the Data Validation dialog box.
- In the Allow field on the Settings tab, select List.
- In the Source field, paste in the range you copied to the clipboard a few seconds ago.
- Put your cursor IMMEDIATELY after the = sign, and type Lists! You should end up with something like: =Lists!$A$2:$A$7 (Lists is the name of the worksheet you are referencing and the ! tells Excel to look for that cell range on the Lists worksheet, not the current one. Make sure you type the worksheet name correctly, otherwise it won’t work — List is not the same name as Lists.)
- Click OK to save your changes and close the Data Validation dialog box.
- As soon as you click OK, you should see a drop-down arrow next to the cell you’re in.
- Click the arrow to test that the list displays as you expect it to.
- If it works as you expect, apply this range to other cells in this column by dragging the little square icon at the bottom right of the cell down the column to cover as many rows as you want. For example, if your mail merge dataset typically has 20 individuals, then drag it down 20 rows.
- Save your changes.
Quick way to link to the same list range but for different columns
If you have several lists set up on the Lists worksheet, then instead of going through many of the steps above, you can just do the steps once, then, once you have your initial range (e.g. =Lists!$A$2:$A$7), you can copy that into other Data Validation dialog boxes and change the column letter and row numbers to reflect the other list. For example, let’s say you have another set of items for a drop-down list that are in column E, rows 2 to 12 — change the ‘A’ in =Lists!$A$2:$A$7 to ‘E’ and the ‘7’ to ’12’, giving you =Lists!$E$2:$E$12. Leave everything else as it is.
Alternative method
Based on the post above, Peter Sanders made this short video on using Range Names instead: http://www.screencast.com/t/jKKSD7zeDzac Being an Excel novice, I knew there would be a more elegant solution than mine, and Peter has provided it. Thanks for your contribution, Peter!
See also:
[Links last checked June 2011; thanks to @michaelhughesua for his conference session related to this, and to Peter Sanders for the video]
CyberText Newsletter 