Solved

VLookup in MS Word

Posted on 2014-01-27
19
8,252 Views
Last Modified: 2016-09-30
I have a MS Word document that I am trying to figure out how to get an action like the VLookup in MS Excel. In the Word document, I have a table set up that is being uses as a form. It has text entry fields as well as check boxes.  It also has a drop down box that will let you select from a list. What I would like to do is have another field populate depending on what is selected from the list.

For example. The drop down list will have items that are selected as what we call risk factors. The risk factor selection could be "lead time is 4 weeks", "lead time is 4 to 8 weeks", "lead time is more than 8 weeks". there would be a score to match each selection.  This score would populate another field in the table depending on what is selected. The score would be 1, 2, 3.
If "lead time is for 4 weeks" is selected, a "3" would populate in the other field. If "lead time is 4 to 8 weeks" is selected, a "2" would populate the other field and if "lead time is more than 8 weeks" a "1" would populate the other field.

Is there an easy way to do this? Or, should I just resolve to making the form in Excel? I already have to form made in MS Word and am just trying to keep from remaking it in Excel but I will if it is easier.
0
Comment
Question by:bgfullerton
19 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 39812632
I would go for InfoPath (part of the MS Office Suite)
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39812996
It can be done using VBA.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39813100
As MacroShadow says, VBA will be necessary. The point about that is that you will need to have (or acquire) some skills in that technology.

There are some zones on EE where it is routine to write complete code to the asker's requirement. Currently, in this zone, we try to show you how, as opposed to doing it for you.

Any advice we give would depend on what technology you are using for your dropdown - FormField, ActiveX combo, or Content Control.

If you are using VBA, you could also consider having all the selection technology on a code-backed VBA userform, so that the final document is (formatted) text-only.
0
 

Author Comment

by:bgfullerton
ID: 39813163
I have infopath on my computer but very few of the others do. So that option is probably out.  I do not know how to do anything in VBA so that option is not very likely either.  I really do not expect to have someone else do my work for me. I do appreciate all of your input but judging from this, it looks like I will be relegated to doing this in Excel. I am going to wait a day or two to see what other responses come back.  Thank you again for your input.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39813174
Don't take my caveats as a 'No'.

It is perfectly possible in Word with VBA, but we need to know the things asked in order to tailor the advice.
0
 

Author Comment

by:bgfullerton
ID: 39813238
I am using the "Drop Down List Content Control" for the selection menu. My goal was to be able to pick a selection from that and have another field populate with the number. Big picture is to then add up the numbers from all the selections and get a score.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39813320
You will need a table where each 'row' can be chosen from the selection made in the content control. From that row the data for the each sdpeciic field can be chosen.

The table can be in an Excel spreadsheet, a Word document or an Access (or any other database) table.
0
 

Author Comment

by:bgfullerton
ID: 39813368
I made a table at the bottom of the form with the selection in one column and the score number in the column to the right. What method do I use to link the selection above to the selection in this table and populate the number into the form?

I apologize if I sound like I may be asking a dumb question but I am not a high end user of MS Word. I really do appreciate your help.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39813555
Perhaps I have visions of grandeur, but I see it as our task to lead you from your novice or, perhaps, somewhat inexperienced status to one with some overall comprehension of the subject, in this case of VBA.

Here is a way to set one Content Control's value according to the choice from another.
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim cc As ContentControl
    If ContentControl.title = "Risk" Then
        Select Case ContentControl.Range.Text
            Case "lead time is 4 weeks"
                Set cc = ActiveDocument.SelectContentControlsByTitle("4 weeks")
                cc.Range.Text = "selected"
            'Case "sagsdfg"
            '...
         End Select
    End If
End Sub

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39813564
Can you post your current document, in case we have major misunderstandings about the layout of your own document?
0
 

Author Comment

by:bgfullerton
ID: 39814853
I will give the VBA a try. Thank you.

Here is a partial of the document I am working on.  The highlights in red are the areas I am referring to. The table at the bottom is where the drop down data will reference to get the number value.
Order-Entry-Checklist.docx
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39815108
I see that you want to look up the selected text in a Word table, find the text in the next cell and then update a bookmarked location with the text.

This code works provided that the text is an exact match between the dropdown content control and the table (it isn't always in your document).
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim rngBookmark As Range
    Dim rngCell As Range
    Dim rng As Range
    Dim rw As Row
    Dim strBookmarkName As String
    Dim tbl As Table
    'Exit Sub
    strBookmarkName = "Risk"
    If ContentControl.Title = "QuotedLeadTime" Then
        Set tbl = ActiveDocument.Tables(6)
        Set rng = tbl.Range
        With rng.Find
            .Text = ContentControl.Range.Text
            If .Execute Then
                Set rw = rng.Rows.First
                Set rngBookmark = ActiveDocument.Bookmarks(strBookmarkName).Range
                Set rngCell = rw.Cells(2).Range
                rngCell.MoveEnd wdCharacter, -1
                rngBookmark.Text = rngCell.Text
                ActiveDocument.Bookmarks.Add strBookmarkName, rngBookmark 're-add bookmark in case it was deleted
            Else
                MsgBox """" & ContentControl.Range.Text & """ not found in table"
            End If
        End With
    End If
End Sub

Open in new window

0
 

Author Comment

by:bgfullerton
ID: 39815194
This worked perfectly.  I figured out I needed to change the table number to match the number of tables I have in my current document. Even though I am a total novice at this, I can read thru this and kind of see what you are doing. One question now is, do I just copy this and add it again below the current text and change the appropriate data to get it to work for the remaining drop downs I will have in the document? It looks like I will have to give each of the drop downs I want to use this for a unique name and then replace that name in the VBA accordingly.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 39815348
You will need some way of saying which content control provides the data, so yes, it is best to give them unique Titles (or Tags). The code below shows how to add similar functionality for extra drop-downs.
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim rngBookmark As Range
    Dim rngCell As Range
    Dim rng As Range
    Dim rw As Row
    Dim strBookmarkName As String
    Dim tbl As Table
    
    Select Case ContentControl.Title
        Case "QuotedLeadTime"
            strBookmarkName = "Risk"
        Case "Price bracket"
            strBookmarkName = "bmk2"
            '...
    End Select
    
    Set tbl = ActiveDocument.Tables(6)
    Set rng = tbl.Range
    With rng.Find
        .Text = ContentControl.Range.Text
        If .Execute Then
            Set rw = rng.Rows.First
            Set rngBookmark = ActiveDocument.Bookmarks(strBookmarkName).Range
            Set rngCell = rw.Cells(2).Range
            rngCell.MoveEnd wdCharacter, -1
            rngBookmark.Text = rngCell.Text
            ActiveDocument.Bookmarks.Add strBookmarkName, rngBookmark
        Else
            MsgBox """" & ContentControl.Range.Text & """ not found in table"
        End If
    End With
End If

Open in new window

0
 

Author Closing Comment

by:bgfullerton
ID: 39815408
Great feedback.  Thank you for your help
0
 

Author Comment

by:bgfullerton
ID: 39815667
I tried figuring this out on my own but have not been able to so far. The first VBA script you gave me worked great. I pasted the second one in and changed the bookmark names to match what I have in the document and it keeps coming up with the error of "Compile error: End If without block If" I tried adding the "End Sub" at the end but that did not help. Note the bookmarks have changed to allow for unique names for each. I have attached the document for review. The areas marked in red are the bookmarked fields and the drop downs I am working with. I was going to just add one at a time to make sure everything continues to work as I move along.  I thank you for your help.
Jobs-Order-Entry-Checklist.docx
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39815981
Sorry I put 'End If' instead of 'End Sub'.
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim rngBookmark As Range
    Dim rngCell As Range
    Dim rng As Range
    Dim rw As Row
    Dim strBookmarkName As String
    Dim tbl As Table
    
    Select Case ContentControl.Title
        Case "QuotedLeadTime"
            strBookmarkName = "Risk"
        Case "Price bracket"
            strBookmarkName = "bmk2"
            '...
    End Select
    If strBookmarkName <> "" Then
        Set tbl = ActiveDocument.Tables(6)
        Set rng = tbl.Range
        With rng.Find
            .Text = ContentControl.Range.Text
            If .Execute Then
                Set rw = rng.Rows.First
                Set rngBookmark = ActiveDocument.Bookmarks(strBookmarkName).Range
                Set rngCell = rw.Cells(2).Range
                rngCell.MoveEnd wdCharacter, -1
                rngBookmark.Text = rngCell.Text
                ActiveDocument.Bookmarks.Add strBookmarkName, rngBookmark
            Else
                MsgBox """" & ContentControl.Range.Text & """ not found in table"
            End If
        End With
    End If
End Sub

Open in new window

0
 

Author Comment

by:bgfullerton
ID: 39816027
Beautiful, I cannot thank you enough. I worked around the issue by doing the copy and paste of the larger segment but this new one looks a lot better and worked like a charm.
0
 

Expert Comment

by:Angela Sangweni
ID: 41823452
I have created a form in word, but i now need it to reference to another word document and pull the fields i need.
It should do something similar to Vlookup on excel,is there a script for that?
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now