Create drop down list if condition is true

Question

I have an excel question. Say I have a list that looks like this:

Fruits    | Apples   | 1
          | Oranges
          | pears

Vegetables| Corn     | 1
          | Turnips
          | Broccli

So, we have columns A,B, and C. A being Fruits or vegetables, B type, C having 1 for every time A is populated. What I want to do is create a drop down where C =1 until the next 1 in cell C.

So what I will end up with is this:

Fruits|Apples(drop down with other fruits)|1

Vegetables|Corn(drop down with other veggies)|1.

Note "|" is the delimiter between cells in case of any confusion.

I hope my question was clear enough for any kind person's help.

Thanks much.

Do you have so many of these that it's unrealistic to name the range and manually create drop down list? In other words, do you really need to automate this based on the '1' field? — Automate This
Haha...yes indeed...unique instances are probably in the 1000's....:/ — joshE

Netloh Netloh · Accepted Answer · 2013-10-25T04:56:23

I am not sure where you want to place these drop downs. But assuming that you would want them on a different sheet in you workbook, you could use the following macro.

Option Explicit

Sub CreateDropdownsInSheet()
    Dim SourceSheet As Worksheet
    Dim TargetSheet As Worksheet
    Dim i As Double
    Dim DropDownRange As Range

    'Define Source Sheet and Target Sheet
    Set SourceSheet = ActiveWorkbook.Sheets("Sheet1")
    Set TargetSheet = ActiveWorkbook.Sheets("Sheet2")

    'Loops through the source sheet
    For i = 1 To SourceSheet.Cells(SourceSheet.Rows.Count, 3).End(xlUp).Row Step 1
        If SourceSheet.Cells(i, 3).Value = 1 Then
            'Gets range for dropdown
            Set DropDownRange = SourceSheet.Range(SourceSheet.Cells(i, 2), SourceSheet.Cells(i, 2).End(xlDown))

            With TargetSheet.Cells(TargetSheet.Cells(TargetSheet.Rows.Count, 1).End(xlUp).Row + 1, 1)
                'Names group
                .Value = SourceSheet.Cells(i, 1).Value

                'Populates dropdown
                With .Offset(0, 1).Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & DropDownRange.Parent.Name & "!" & DropDownRange.Address(External:=False)
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .ShowInput = True
                    .ShowError = True
                End With

                'Selects first element in dropdown list
                .Offset(0, 1).Value = SourceSheet.Cells(i, 2).Value
            End With
        End If
    Next i
End Sub

If you change the sheet names of the SourceSheet and the TargetSheet variables in the code to fit your workbook (I assume that the target sheet is an empty sheet) and run this macro, the list you are looking for should be created in the target sheet.

Create drop down list if condition is true

1 Answers