Equivalent of VLOOKUP() in Word
Raphaeldu68
Posted messages
340
Status
Member
-
Patrice67 -
Patrice67 -
Hello,
I am looking to insert a function equivalent to the VLOOKUP() in Excel into Word.
Here is the goal of the document: I have a client list in Excel and I want to automatically fill in fields such as Company Name, Responsible Person, Contact Details, Date, ... when I enter the customer code in the designated area on the page. (if possible, automatic updating)
Thank you in advance for your help.
Sincerely,
Raphaël
PS: I am working on MS Word 2010.
Configuration: Desktop PC: Windows XP SP3 - Firefox 3.6.6
Laptop: dual-boot:
- Linux Ubuntu 10.4 - Firefox 3.6.6
- Windows 7 Home Premium - Firefox 3.6.6
--
It is by making mistakes that we learn not to make them anymore!
I am looking to insert a function equivalent to the VLOOKUP() in Excel into Word.
Here is the goal of the document: I have a client list in Excel and I want to automatically fill in fields such as Company Name, Responsible Person, Contact Details, Date, ... when I enter the customer code in the designated area on the page. (if possible, automatic updating)
Thank you in advance for your help.
Sincerely,
Raphaël
PS: I am working on MS Word 2010.
Configuration: Desktop PC: Windows XP SP3 - Firefox 3.6.6
Laptop: dual-boot:
- Linux Ubuntu 10.4 - Firefox 3.6.6
- Windows 7 Home Premium - Firefox 3.6.6
--
It is by making mistakes that we learn not to make them anymore!
10 answers
Hello,
Yes, I completely agree that Raymond's idea is the right one. Mail merge is not just for bulk mailing.
You need to create your template with the design you want.
Then, under the Mailings tab, click on the Select Recipients button, and choose your Excel file.
Insert the address block and/or all the merge fields you need (name, address, contact, etc.) where you want.
Once everything is set up, save it. To use it, open it, then under the Mailings tab, click on the Edit Recipient List button, deselect everything, and choose the client of your choice from the client code list. If there are many, you can use the "Find a Recipient" button.
Then just click on Finish & Merge to generate your Word document with all your client's information.
m@rina
--
- "On the office forum, we ask questions about office software..."
- "Really???"
Yes, I completely agree that Raymond's idea is the right one. Mail merge is not just for bulk mailing.
You need to create your template with the design you want.
Then, under the Mailings tab, click on the Select Recipients button, and choose your Excel file.
Insert the address block and/or all the merge fields you need (name, address, contact, etc.) where you want.
Once everything is set up, save it. To use it, open it, then under the Mailings tab, click on the Edit Recipient List button, deselect everything, and choose the client of your choice from the client code list. If there are many, you can use the "Find a Recipient" button.
Then just click on Finish & Merge to generate your Word document with all your client's information.
m@rina
--
- "On the office forum, we ask questions about office software..."
- "Really???"
Hello
With a macro of this type, you can retrieve data from Excel and bring it into Word:
Example of a macro to put in a Word document
Principle:
We open an instance of Excel.
We open the Excel file from its directory.
We write in Word the data located, for example, in sheet 1, row 1, column 1
We close the file and the Excel application
From there, we can do pretty much what we want.
Important point: you need to initiate the Excel library from the Word VBE editor (Alt+ F11) ==> Tools/references/check Microsoft Excel12.0 Object library
If you need more explanations, feel free to ask.
Best regards
Patrice
With a macro of this type, you can retrieve data from Excel and bring it into Word:
Example of a macro to put in a Word document
Private Sub Document_Open() Dim appXl As Excel.Application Dim ficXl As Excel.Workbook Set appXl = New Excel.Application 'opens the file Set ficXl = appXl.Workbooks.Open("d:\ccm\test.xlsx") 'displays the number of sheets in the workbook Selection.TypeText Text:=appXl.Sheets(1).Cells(1, 1) 'closes the file and quits Excel ficXl.Close appXl.Quit End Sub Principle:
We open an instance of Excel.
We open the Excel file from its directory.
We write in Word the data located, for example, in sheet 1, row 1, column 1
We close the file and the Excel application
From there, we can do pretty much what we want.
Important point: you need to initiate the Excel library from the Word VBE editor (Alt+ F11) ==> Tools/references/check Microsoft Excel12.0 Object library
If you need more explanations, feel free to ask.
Best regards
Patrice
Yes, and it's called mail merge!
You just need to specify to Word that the data is in an Excel file.
--
Retirement is nice! Especially in the Caribbean... :-)
☻ Raymond ♂
You just need to specify to Word that the data is in an Excel file.
--
Retirement is nice! Especially in the Caribbean... :-)
☻ Raymond ♂
Well done Raymond,
And it's super convenient for correspondence.
Unfortunately, that's not what I'm looking for; it's a bit more complicated: I edit my client follow-up sheets in Word*, and for each action (client visit, price request, promotional offer, ...) I have a Word template.
What I'm looking for is when I fill in the "Client Code" field, Word automatically fills in the fields according to an evolving database in Excel.
* Excel is not bad, but doing word processing with a spreadsheet, whose primary function is to calculate, yields an average result, which is normal - that’s why I'm looking to use MS Word.
Nevertheless, thank you very much for your response and have a good afternoon!
Sincerely
Raphaël
--
It's by making mistakes that we learn not to make them anymore!
And it's super convenient for correspondence.
Unfortunately, that's not what I'm looking for; it's a bit more complicated: I edit my client follow-up sheets in Word*, and for each action (client visit, price request, promotional offer, ...) I have a Word template.
What I'm looking for is when I fill in the "Client Code" field, Word automatically fills in the fields according to an evolving database in Excel.
* Excel is not bad, but doing word processing with a spreadsheet, whose primary function is to calculate, yields an average result, which is normal - that’s why I'm looking to use MS Word.
Nevertheless, thank you very much for your response and have a good afternoon!
Sincerely
Raphaël
--
It's by making mistakes that we learn not to make them anymore!
Hello!
I also thought about Mail Merge. Do you have a Word and Excel example to better understand your issue?
Best regards
I also thought about Mail Merge. Do you have a Word and Excel example to better understand your issue?
Best regards
Hello,
Thank you very much for all your responses!
Unfortunately, it is still not what I'm looking for:
Yes, I am well aware of mail merge and as its name suggests, it is perfect for generating a letter and similar documents.
What I am looking for, if it is feasible, is a more precise product: a dynamic sheet with automatic updates. In fact, the principle of the Excel formula (when automatic calculation is not prevented in the settings - manual mode) is to update automatically and immediately based on parameters modified in the sheet, the workbook, or an external Excel file.
This is what I'm looking for - if it exists - in Word: when I open the sheet again, the parameters modified in the Excel file, such as the name of the manager, phone number, email,... update automatically without the need to re-mail merge the document.
I am aware that what I am looking for is very specific and that perhaps it does not exist, which is why I am asking for your help on CCM.
I hope my explanations have helped you better understand what I am looking for. Feel free to ask me questions if necessary!
Thank you again for your help and I wish you an excellent afternoon!
Best regards,
Raphaël
--
It is by making mistakes that we learn not to make them again!
Thank you very much for all your responses!
Unfortunately, it is still not what I'm looking for:
Yes, I am well aware of mail merge and as its name suggests, it is perfect for generating a letter and similar documents.
What I am looking for, if it is feasible, is a more precise product: a dynamic sheet with automatic updates. In fact, the principle of the Excel formula (when automatic calculation is not prevented in the settings - manual mode) is to update automatically and immediately based on parameters modified in the sheet, the workbook, or an external Excel file.
This is what I'm looking for - if it exists - in Word: when I open the sheet again, the parameters modified in the Excel file, such as the name of the manager, phone number, email,... update automatically without the need to re-mail merge the document.
I am aware that what I am looking for is very specific and that perhaps it does not exist, which is why I am asking for your help on CCM.
I hope my explanations have helped you better understand what I am looking for. Feel free to ask me questions if necessary!
Thank you again for your help and I wish you an excellent afternoon!
Best regards,
Raphaël
--
It is by making mistakes that we learn not to make them again!
I don't really believe that exists. In any case, I've never heard of it.
But since we can do amazing things with VBA, it could be that a member of the forum is able to write you a macro that does what you want...
With my best wishes.
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
But since we can do amazing things with VBA, it could be that a member of the forum is able to write you a macro that does what you want...
With my best wishes.
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
Thank you very much for your response, Raymond!
Indeed, the more I search, the more I realize that I will need a Macro to do what I desire; unfortunately, I'm just tinkering with Macros...
If a member of the CCM Community would like to help me, I am always open to it! Call for volunteers...
Otherwise, I will have to get into VBA one of these days, unless I create a printable web form in PHP and use the original database in SQL... I'll think about it... it's a shame because I'm going to lose my Word templates. (the HTML code from Word when exported is so "dirty" that I'd rather start from scratch)
Have a good evening!
Raphaël
--
It's by making mistakes that we learn not to make them anymore!
Indeed, the more I search, the more I realize that I will need a Macro to do what I desire; unfortunately, I'm just tinkering with Macros...
If a member of the CCM Community would like to help me, I am always open to it! Call for volunteers...
Otherwise, I will have to get into VBA one of these days, unless I create a printable web form in PHP and use the original database in SQL... I'll think about it... it's a shame because I'm going to lose my Word templates. (the HTML code from Word when exported is so "dirty" that I'd rather start from scratch)
Have a good evening!
Raphaël
--
It's by making mistakes that we learn not to make them anymore!
Hello Raphaël.
"Excel is pretty good, but doing word processing with a spreadsheet, whose primary purpose is to calculate, gives an average result, - which is normal - that's why I'm looking to use MS Word."
Send me one of your letter templates, the most complicated one () and I'll redo it on Excel: you can compare!
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
"Excel is pretty good, but doing word processing with a spreadsheet, whose primary purpose is to calculate, gives an average result, - which is normal - that's why I'm looking to use MS Word."
Send me one of your letter templates, the most complicated one () and I'll redo it on Excel: you can compare!
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
Raphael, I'm afraid you may not have understood me...
Your various tracking sheet templates are indeed created, enhanced, improved, fine-tuned, in Word.
Excel is only used to provide the data to fill in the fields.
Have you ever practiced mail merge?
--
Retirement is nice! Especially in the Caribbean... :-)
☻ Raymond ♂
Your various tracking sheet templates are indeed created, enhanced, improved, fine-tuned, in Word.
Excel is only used to provide the data to fill in the fields.
Have you ever practiced mail merge?
--
Retirement is nice! Especially in the Caribbean... :-)
☻ Raymond ♂