Search code examples
excelvbavalidationdropdownrows

Pre-populating a dropdown list cell based on a cell entry in the same row (Excel/VBA)?

I am trying to force a "Yes/No" dropdown list (created by data validation from a list elsewhere in the workbook) to pre-populate with "No" when a certain cell on its same row is filled in.

I have the following table:

Table

On the 3rd data entry row, when I fill in the "ID" field with a value, I want the "Bought?" field for that row to pre-populate with "Yes" immediately, rather than having to go into the dropdown list itself and choose "Yes" or "No". This should leave the option for the field to be changed to "No" via the dropdown menu.

Is this something that can be done with standard excel data validation or do I need to use some VBA code?

Solution

  • I’m not sure if you ever got a satisfactory answer to this one, but if it were a VBA solution you were looking for, a Private Sub would achieve what you are after. The following assumes the sheet in question is Sheet1, and the ID column is C.

    Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

If Not Intersect(Range("C:C"), Target) Is Nothing Then
 
Set c = Sheet1.Range(Target.Address)

    If c.Value <> "" Then
        c.Offset(, 1).Value = "Yes"
            Else
            c.Offset(, 1).Value = ""
    End If

End If
End Sub