Excel, creating a dynamic Data Validation dropdown by condition

Question

I have a large list of food info in an Excel sheet. For example, two columns:

Apples Fruit
Beets Vegetable
Spinach Vegetable
Cheese Dairy

I have another sheet with a data validation dropdown that allows you to choose foods from the food info sheet. However, I'd like to be able to narrow the search fields by allowing the user to also select a category that informs the dropdown.

For example, two columns (user enters "Fruit" on the left column, perhaps from a dropdown that has items like Fruit, Vegetable, Dairy, etc):

Fruit (dropdown that contains fruit items from the food info sheet)

I don't know how to make the Data Validation field say "select all food names (from the food info sheet) where the category info (on the food info sheet) equals the category designated by the user (on the sheet with the dropdowns)."

Gary's Student Gary's Student · Accepted Answer · 2015-02-01T18:38:41

Say Sheet2 is like:

enter image description here

In Sheet1 cell A1 put the usual Data Validation including the values Fruit, Vegetables,Dairy
Then put the following event macro in the Sheet1 worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A1 As Range, B1 As Range, sDV As String
    Dim N As Long, i As Long
    Set A1 = Range("A1")
    Set B1 = Range("B1")

    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, A1) Is Nothing Then Exit Sub

    v = A1.Value
    sDV = ""
    With Sheets("Sheet2")
        N = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = 1 To N
            If v = .Cells(i, "B").Value Then
                sDV = sDV & "," & .Cells(i, "A").Value
            End If
        Next i
        sDV = Mid(sDV, 2)
    End With

    Application.EnableEvents = False
    With B1.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=sDV
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Application.EnableEvents = True
End Sub

Now whenever you select a category in cell A1, the proper DV will be setup for cell B1.

Because it is worksheet code, it is very easy to install and automatic to use:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE windows as above
clear the code out
close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Excel, creating a dynamic Data Validation dropdown by condition

1 Answers