1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Vlookup in WORD

Discussion in 'Business Applications' started by Yorkshire Guy, Mar 16, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. Yorkshire Guy

    Yorkshire Guy Thread Starter

    Joined:
    Dec 9, 2003
    Messages:
    563
    Using WinXP with OfficeXP.

    This would save me keying time and be more accurate if it could be done:

    I have a s/s with a few hundred names and addresses, each has a unique 'id no'; 1 row per N&A: Name, address line 1, address line 2 etc in separate columns.

    I routinely have to send individual letters to these people.

    I manually look up a N&A in the Excel s/s, make a note of it, then type it into WORD.
    (cut & paste isn't much quicker).

    What would be really great is if I could type the 'id no' in Word and then for it to do a VLOOKUP against the Excel s/s to find the N&A.

    Mail merge is too messy each time to create a single letter.
    Typing the letter in Excel doesn't isn't great either!

    Any ideas please?

    Thanks.
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    Why not put them all in an Access database and have a form set up that you can use on an individual or group letter basis? All VBA functionality is in Access and can open templates or word documents from code. A lot more simple than flipping between two dissimilar apps to accomplish a single task.
     
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,731
    Or set the document up as a mail merge to the Excel source, and use the QUERY features to get the info you want merged into your doc.

    Save the document as a template and hit File-New each time you want to use it. That way, no previous "query" is saved.
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Or import the Excel file in Outlook, then add the Insert Address button to your Word toolbar.

    Rgds,
    Andy
     
  5. Yorkshire Guy

    Yorkshire Guy Thread Starter

    Joined:
    Dec 9, 2003
    Messages:
    563
    Thanks to all of the above:

    Rockn - yes I keep coming up with the Access solution, and one day I will have to bite the bullett and convert.

    Dreamboat, I didn't want to have to do a Mail Merge with a Query, by the time I've done that I may as well have just typed the N&A.

    XLGuru, I hadn't thought about using Outlook address book, it would need constant re-importing as new N&A's are added to the s/s - I'll have to have a look into this.


    Please consider this Thread closed as unless there was a simple answer I don't want to waste anyone's time.

    Thanks
    Michael

    PS you know what I really find great with this Forum - I scan a lot of the responses you guys and gals post against other peoples problems and find so much interesting stuff.
    How did you get so expert - don't answer that, just keep up what you are doing.
    Thanks again.
     
  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Michael, I didn't get where I am today by not wasting my time. ;)

    "Mail merge is too messy each time to create a single letter" suggests you're missing something. I'm far from a Word fan, however I do ALL my form letters AND scratch letters AND envelopes using a table doc as the mailmerge source. You only have to make the "client" docs mailmerge docs one time.

    Let's say there's a valid reason for keeping the N&As in Excel. "Each has a unique id no", and you want to "type the 'id no' in Word then have it do a VLOOKUP against the Excel s/s to find the N&A". However, you have "a few hundred names and addresses". How do you know which ID relates to which N&A without physically looking in Excel?

    Honestly, if you must keep the Excel link, then make the doc(s) mailmerge(s). You can add the Find Record button to your menu bar & customise its name to &Look. That way, you can hit ALT+L to show it, type the ID#, hit Enter once (or twice at worst), then Esc. Et voila, your ID#/Name/Address is in your doc.

    Rgds,
    Andy
     
  7. Yorkshire Guy

    Yorkshire Guy Thread Starter

    Joined:
    Dec 9, 2003
    Messages:
    563
    XL Guru

    OK, you've convinced me to try the mailmerge solution, I'll have a go later tonight.

    Thanks once more.

    PS. Yes, I have to look up the 'id no' first since I generally only have their name to begin with (the s/s is alphabetical by name), [I've seen a few threads re wanting a quick way to 'tab' down to a specific entry, in my case say jump down to the row for Bloggs,Fred - but I don't think there's been an easy way to do that has there? Different sheets per 'tab' isn't a solution for me, I have to have the whole datbase list in one sheet].

    Having located the correct row, what I wanted to avoid is:
    copy the Name cell, paste into Word, copy the Address Line 1 cell, paste into Word, copy... etc.

    Best regards
    Michael
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> OK, you've convinced me ... I'll have a go later tonight

    Good for you ; you won't regret it (I know, I'm gonna regret that).

    If you really can't get to grips/comfortable with it, it's dead easy to attach a tiny macro to a button. So that when you click the button,

    1. Excel grabs the row #
    2. A handful of formulas use the # to create a vertical array of the (horizontal) cells
    3. This gets copied to the clipboard.

    Then back in Word, you just Paste Special as unformatted text.

    Rgds,
    Andy
     
  9. Yorkshire Guy

    Yorkshire Guy Thread Starter

    Joined:
    Dec 9, 2003
    Messages:
    563
    Andy,

    Thanks again, I'll try that latest suggestion.

    I started on the Mail Merge but as I've just upgraded to Office XP have now discovered how different Mail Merge now is!

    I'm away for a few days so won't be able to update 'till next week.

    Cheers,
    Michael
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/212249