How to approach the need for "nested" dropdowns (2nd dropdown depends on 1st dropdown value selected)

[OaklandJim]

This is a classic. I have a list of insurance providers and a list of corresponding plan types for the providers, both in a sheet named Drug Plans. In another worksheet (named Drug Comparison) I have a data validation dropdown listing all providers (based on the values in the Drug Plans worksheet). When user selects a provider I need a second data validation that only lists the relevant plan types (based on the values in the Drug Plans worksheet) for the provider selected.

Providers are listed in a range named Providers (range B5:B24) and the plan types are listed in a range named Plans (range C5:C24), both in a sheet named Drug Plans.

es.xlsm
	A	B	C
3
4		Provider	Plan
5	1	Anthem	Basic
6	2	Anthem	Plus
7	3	Blue Shield	Plus
8	4	Blue Shield	Enhanced
9	5	Humana	Value RX
10	6	Humana	Premier
11	7	SilverScripts	Choice
12	8	Wellcare	Value Script
13	9	Wellcare	Classic
14	10	Wellcare	Value Plus
15	11	Cigna	Saver Rx
16	12	Cigna	Assurance
17	13	Cigna	Extra
18	14	UHC	Preferred
19	15	UHC	Saver
20	16
21	17
22	18
23	19
24	20
Drug Plans

Might you suggest an approach to setting up the second dropdown to include just the relevant plan type(s)?

Jim

 

[dreid1011]

Point your second dropdown to a list generated with a LOOKUP/FILTER function based on the value selected in the first dropdown. This secondary list can be hidden on a different sheet or far enough out of the way so it's not visible too if that is desired.

 

[OaklandJim]

I have several of these so I was hoping for a way to specify the array of plan types directly in the data validation Source field.

I wrote a simple UDF (below) that returns the array of plan types desired. But when I try to use the UDF with data validation (in Source field) an error is generated, something like a named range cannot be found. So, instead of feeding a named range into the function I use a cell reference. That generates the same error message even though I am not using a named range for the parameter.

Option Explicit
Option Base 1

Function PlansList(psProvider As String) As Variant

    Dim rProviders As Range

    Dim rPlans As Range

    Dim rCell As Range

    Dim iPlanIndex As Long

    Dim iArrayIndex As Long

    Dim asPlansList() As String

    Set rProviders = [DrugPlans].Range("Providers")

    Set rPlans = [DrugPlans].Range("Plans")
    
    For Each rCell In rProviders

        iPlanIndex = iPlanIndex + 1

        If rCell.Value = psProvider _
         Then
            iArrayIndex = iArrayIndex + 1

            ReDim Preserve asPlansList(iArrayIndex)

            asPlansList(iArrayIndex) = rPlans.Cells(iPlanIndex)

        End If

    Next rCell
    
    PlansList = WorksheetFunction.Transpose(asPlansList())
    
End Function

I guess a UDF cannot be used for the Source in data validation?

Jim

 

[dreid1011]

Not as the source, no. But you should be able to use the cell range where the UDF is returning its list as the source. I essentially suggested the same thing, but instead of a UDF, I suggested LOOKUP/FILTER to create the plan list. I've done this once or twice so I'm not sure what error you are getting when trying to use a cell reference. Could you try it with the cell reference and screen shot the error?

 

[OaklandJim]

First, I really appreciate the assistance. It means a lot to me.

The error occurred when I tried to use my UDF as the source for data validation.

If I use the UDF in the RefersTo range of a defined name and try to use the name in the Source field I get a different error.

I found another issue with the approach where I use my UDF (or your formula suggestion) to generate the list of plan types in a range of cells. The problem is that there is a variable number of possible values (plan types) for the the dropdown although I need to define the ranges so they are sized to contain the maximum number of plan types so sometimes I'll get a drop down (data validation) with blanks.

I guess that I need to go down the road of generating the Source list as a csv list for data validation source using vba and event handler. More work than I had hoped and not very satisfying.

Jim

 

[LegendaryAxe]

Potential fix: Try wrapping the named range in an indirect function . So =PlansList(INDEX(INDIRECT("Providers"),1)). I've had issues before with using named ranges in data validation formulas and this has fixed it in the past.

 

[OaklandJim]

Thanks for the reply. I had already gone down the road of using change event handler and a call to a sub to accomplish this. Maybe there is an Excel enhancement involving use of a function that returns an array.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rProviders As Range

    Set rProviders = Me.Range("Providers")

'   The following code calls a sub that sets the data validation list for
'   the cell just below the changed cell, if the changed cell is a provider
'   name (i.e., changed cell is in the range named Providers that contains
'   the list of providers).
    If Not Application.Intersect(Target, rProviders) Is Nothing _
     Then
        Call FillPlanTypes(Target)
    End If
    
End Sub

Option Explicit

' ----------------------------------------------------------------
' Procedure Name: FillPlanTypes
' Purpose: Fill plan types cell for cell below the one that contains provider name.
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter rProviderCell (Range): The single celled range that is the Target from event handler.
' Author: Jim
' Date: 2/21/2025
' ----------------------------------------------------------------
Sub FillPlanTypes(ByVal rProviderCell As Range)

'   Array containg the list
    Dim avPlansList() As Variant

'   Holds csv list of plan types for the respective provider to specify
'   the list of plans for data validation.
    Dim sList As String
    
'   Used to access array elements.
    Dim iIndex As Long
    
    sList = ""

'   Use function PlansList to gather the plans list (for a provider)
'   and put the array returned into the local array variable.
    avPlansList = PlansList(rProviderCell.Value)

'   Loop all array elements.
    For iIndex = 1 To UBound(avPlansList)

'       Add the current plan type to the string containing the csv list
'       for data validation.
        sList = sList & avPlansList(iIndex)

'       Add comma if not the final plan type in the list.
        If iIndex < UBound(avPlansList) _
         Then
            sList = sList & ","
        End If

    Next

'   Must unprotect to change validation off to add data validation list.
    rProviderCell.Parent.Unprotect
    
'   Put list into the data validation dropdown for the respective plan types cell.
    With rProviderCell.Offset(1).Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, _
             Formula1:=Trim(sList)
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    rProviderCell.Parent.Protect UserInterfaceOnly:=True
    
End Sub