• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15916
  • Last Modified:

VLookup in MS Word

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
bgfullerton
Asked:
bgfullerton
1 Solution
 
x-menIT super heroCommented:
I would go for InfoPath (part of the MS Office Suite)
0
 
MacroShadowCommented:
It can be done using VBA.
0
 
GrahamSkanRetiredCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
bgfullertonAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
bgfullertonAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
bgfullertonAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
GrahamSkanRetiredCommented:
Can you post your current document, in case we have major misunderstandings about the layout of your own document?
0
 
bgfullertonAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
bgfullertonAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
bgfullertonAuthor Commented:
Great feedback.  Thank you for your help
0
 
bgfullertonAuthor Commented:
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
 
GrahamSkanRetiredCommented:
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
 
bgfullertonAuthor Commented:
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
 
Angela SangweniCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now