Mail merge of 2 worksheets, using 2nd sheet for criteria to merge

[Angela]

I am trying to mail merge an Excel file containing 2 worksheets into a
letter created with Word. Sheet1 contains all contact information
that will be merged into the header of the letter. Sheet2 contains
the criteria that will select which rows to merge. For instance,
"Date Received" is a column header in Sheet2. We want to merge rows
that have 9/10/2003 in the column "Date Received." Right now we are
using a workaround by putting an extra column in sheet1 and manually
typing an "X" in the rows we want to merge. (then selecting Query
Options before running the merge)

Is there any way to automate this better? Either maybe using MS Query
or just a formula that will put the "X" in the column for us?

Thanks for any help you could provide.

Angela

 

[Debra Dalgleish]

You can use a formula to add the X to qualifying rows in Sheet1. For
example, if the date to check is in Sheet2!A2, and the record dates are
in column B on Sheet1 -- =IF(B2=Sheet2!$A$2,"X","")

Copy the formula down to the last row of data.

In Word, use the Query Options to filter for records with an X in the
new field.

 

[Dave Peterson]

I'm not sure how your data is laid out or what it looks like, but maybe you
could "automate" that X.

There's a function called =vlookup() that can "merge" data based on a common
(but unique) identifier.

So if you had a contact id that appeared in column A of sheet1.

And in sheet2 you had the same id in column A, and the date received in column
B, you could use this as your formula:

=vlookup(a1,sheet2!$a$1:$b$9999,2,false)

Then filter on that column in your merge.