asked on

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.

x-men

I would go for InfoPath (part of the MS Office Suite)

Joe Howard

It can be done using VBA.

GrahamSkan

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.

bgfullerton

ASKER

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.

GrahamSkan

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.

bgfullerton

ASKER

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.

GrahamSkan

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.

bgfullerton

ASKER

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.

GrahamSkan

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

GrahamSkan

Can you post your current document, in case we have major misunderstandings about the layout of your own document?

bgfullerton

ASKER

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

GrahamSkan

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

bgfullerton

ASKER

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.

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bgfullerton

ASKER

Great feedback. Thank you for your help

bgfullerton

ASKER

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

GrahamSkan

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

bgfullerton

ASKER

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.

Angela Sangweni

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?