I am working on a project that utilizes client and vendor data. Basically, once a client's contract is awarded to the vendor, I can enter a key (a 3 letter acronym of the vendor name) on the client sheet, that matches with a row of values describing the vendor. This key in turn populates the next 8 or so fields of the client sheet with data (mailing block, etc) pulled from the vendor sheet.

Currently, I am using lookup function on the client sheet (where AQ2 = vendor key, entered on Client!) with this formula (in different flavors):

=LOOKUP(AQ2, Vendor!A:A, Vendor!H:H)

This works, for one vendor; so I can send off the contract award letter and stuff like that to the one winner. However, is there is a way to send letters to all the people who don't win the contract?

So say 4 vendors bid, 3 need "decline" letters; but the 3 sets of information need to be managed from 1 sheet whose keys are Client! based just like the "award";

  • How do I use lookup or another analogous function to import the information for a mail merge for multiple recipients based on a client key spreadsheet (Is it possible?) ? (This would be in addition to the initial 'award' lookup; thought not necessary at the same time.)
  • I would greatly appreciate that any necessary clarifications be asked of me so that I can shore up this question, see related meta. If you do understand the two questions, and could submit an edit to clarify what I seem to be missing, it would be greatly appreciated – mfg Apr 21 '11 at 14:18
  • I don't really know much about this type of excel work, though I've figured out quite a few excel problems before, but if I have some free time later, might look into it. I think the problem is more a lack of excel-specific expertise on Super User rather than with your question specifically. – nhinkle Apr 22 '11 at 3:56
  • Is the mailing block the same, except for the vendor specifics? So this is essentially setting up and being used like a mailmerge document? Is Word involved in the letter being sent? – datatoo Apr 25 '11 at 16:54
  • @datatoo the mailing block is the same. It is being rendered through mail merge in a Word document. Caveat: The final document uses merge info from both Client! and Vendor! , hence needing to pull both on to one sheet (and there are about 200 vendors vs 20-40 clients). – mfg Apr 25 '11 at 17:00

There may be a more "elegant" way of doing it, but if you are able to do the mail merge with one row pulled from the lookup function, you should be able to do it multiple times using some VBA to loop through the declined companies, filling their information into a worksheet, then calling a mail merge based on that.

I don't know VBA myself, but the logic would be something like:

for each row in Vendor!
    if row.VendorCode = Client!VendorCode, skip this record
    put row.VendorCode in DeclinedMailMerge!VendorCode
    do the mail merge on DeclinedMailMerge!

You should explore using an advanced filter macro. Set the criteria to exclude the awarded vendor such as <>ACME and the resultant returned data is your mail merge source. You can also use the same sheet for the award letter, with a different merge document. and have a client selector combobox to manage that selection and population of content for each type of letter.

The advanced filter, once you have it setup of is easily repeatable, programmable and likely quicker than a loop would be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.