How to autopopulate excel column based on dropdown list selection from another column

Question

I have a spreadsheet that has two buttons - To retrieve rows from a sql table using a macro and another one to update data changes back to the table from excel. I have also attached an image here for reference. The table columns are EmpID, EName, Grouping, CCNum,CCName, ResTypeNum, ResName and Status. Now to update changes, I have drop down lists for CCName and ResName. I would like the ResTypeNum to change automatically whenever the value in ResName column from dropdown list changes. Using Vlookup doesn't seem to work as the formula gets wiped out every time I click on the Retrieve button to refresh data. Also, I have to drag down the formula which I don't want but instead the value in ResTypeNum should automatically update whenever the ResName column is changed. I would appreciate any new ideas to make this work.

Thank you, Hema

Yo can achieve this by placing a function in the worksheet change event to fire on changes to column G and write the appropriate value on column H in the same row. No formula needed. — nbayly
Can you elaborate on it a little more? How to lookup the appropriate value without the vlookup and autopopulate at the same time? — Hema
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("G4:G90")) Is Nothing Then ----------- End If End Sub — Hema
I'm not sure how to lookup values if the data changes in column G. Any help is appreciated. Thanks!! — Hema
Where is this appropriate value? I take it its some kind of list on a different worksheet with just 2 columns? Does this list change at all? You could write that list out in your VBA code if it does not change hardly. Or there are numerous ways to replicate functionality of a vlookup such as range.find or you could just loop through the range, find the match and return the value from the second column. — nbayly

nbayly nbayly · Accepted Answer · 2017-07-10T21:23:57

Assumptions:

First value is in A4
ResName column is G
Sheet with data validation list and corresponding code in sheet "ResNameSheet"

In the tables sheet event module you place the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 And Target.Row > 3 Then

    If Target.Value2 = "" Then
        Target.Offset(0, -1).Value2 = ""
        Exit Sub
    End If

    Dim rngDataValidation As Range
    Dim rngRow As Range
    Set rngDataValidation = ThisWorkbook.Sheets("ResNameSheet").UsedRange

    For Each rngRow In rngDataValidation.Rows

        If rngRow.Cells(1, 1).Value2 = Target.Value2 Then
            Target.Offset(0, -1).Value2 = rngRow.Cells(1, 2).Value2
        End If

    Next

End If

End Sub

Explaining how the code works:

Fires on changes to the sheet
checks if changes are in column G (7) and that it occurs below the header row (3)
Checks that the change was not deleting from column G, if it is it clears all values on the corresponding column F
Loops through the Rows collection in the range with ResName list
Checks if values match
If it does it writes the corresponding code to the column to left of Target

Hope this helps.

How to autopopulate excel column based on dropdown list selection from another column

1 Answers