Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Vlookup in WORD


(!)

Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
16-Mar-2004, 08:34 AM #1
Vlookup in WORD
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.
Rockn's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
16-Mar-2004, 01:05 PM #2
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.
__________________
My no line signature
a
a
Anne Troy's Avatar
Computer Specs
Member with 11,731 posts.
 
Join Date: Feb 1999
Location: Allentown, PA
Experience: Intermediate
16-Mar-2004, 04:42 PM #3
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.
XL Guru's Avatar
Senior Member with 2,702 posts.
 
Join Date: Aug 2003
Location: nr. Cambridge, England.
Experience: there's no substitute for it, apparently
16-Mar-2004, 06:31 PM #4
Or import the Excel file in Outlook, then add the Insert Address button to your Word toolbar.

Rgds,
Andy
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
17-Mar-2004, 08:47 AM #5
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.
XL Guru's Avatar
Senior Member with 2,702 posts.
 
Join Date: Aug 2003
Location: nr. Cambridge, England.
Experience: there's no substitute for it, apparently
17-Mar-2004, 11:50 AM #6
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
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
18-Mar-2004, 08:15 AM #7
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
XL Guru's Avatar
Senior Member with 2,702 posts.
 
Join Date: Aug 2003
Location: nr. Cambridge, England.
Experience: there's no substitute for it, apparently
18-Mar-2004, 09:57 AM #8
>> 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
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
19-Mar-2004, 10:27 AM #9
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑