Dependent dropdown list in Excel ,Data Validation and VBA

Question

When I try to paste a long code into the data validation source, I am unable to do so. To solve this problem, I created a function in VBA, but after calling it in the data validation source, I keep getting the same error: A named range you specified cannot be found. I would like to create a dependent dropdown list using an IF condition or find another way to solve the issue.

This is the code I attempted to paste into the data validation source:

=IF(AND(B5="Single_Core", B7="Laid in Free Air", B8="Trefoil"), I3, 
   IF(AND(B5="Single_Core", B7="Laid in Free Air", B8="Flat"), I4, 
   IF(AND(B5<>"Single_Core", OR(B7="Laid in Free Air", B7="Laid in Duct")), i1, 
   IF(AND(B5="Single_Core", B7="Laid in Ground"), i2, 
   IF(AND(B5<>"Single_Core", B7="Laid Direct in Ground"), i2, "")))))

i1,i2,i3,i4 are defined in another sheet contain all lists and this is the code i use it in the VBA Module:

Function InstallationType(B5 As String, B7 As String, B8 As String) As String
    If B5 = "Single_Core" And B7 = "Laid in Free Air" And B8 = "Trefoil" Then
        InstallationType = "I3"
    ElseIf B5 = "Single_Core" And B7 = "Laid in Free Air" And B8 = "Flat" Then
        InstallationType = "I4"
    ElseIf B5 <> "Single_Core" And (B7 = "Laid in Free Air" Or B7 = "Laid in Duct") Then
        InstallationType = "I1"
    ElseIf B5 = "Single_Core" And B7 = "Laid in Ground" Then
        InstallationType = "I2"
    ElseIf B5 <> "Single_Core" And B7 = "Laid Direct in Ground" Then
        InstallationType = "I2"
    Else
        InstallationType = ""
    End If
End Function

The Main Task:

rotabor · Accepted Answer · 2024-10-18 18:32:14Z

2

For list data validation, the formula should return the range. So, your formula should be like this:

=IF(AND(B5="Single_Core", B7="Laid in Free Air", B8="Trefoil"), I1:I4, 
   IF(AND(B5="Single_Core", B7="Laid in Free Air", B8="Flat"), J1:J4, 
   IF(AND(B5<>"Single_Core", OR(B7="Laid in Free Air", B7="Laid in Duct")), K1:K4, 
   IF(AND(B5="Single_Core", B7="Laid in Ground"), L1:L4, 
   IF(AND(B5<>"Single_Core", B7="Laid Direct in Ground"), M1:M4, N1:N1)))))

The idea is what different ranges (I1:I4, J1:J4, ...) are used to provide list values upon conditions.

May be, you need something else instead of list validation?

It seems the UDF can't be used as the list provider.

Optimized formula:

=IF(OR(B7="Laid in Ground",B7="Laid Direct in Ground"),
  Ground_Installation,
  IF(B5="Single_Core",
    IF(B8="Trefoil",
      Free_Single_Air_Trefoil,
      IF(B8="Flat",Free_Air_Single_Flat,EmptyCell)
    ),
    IF(OR(B7="Laid in Free Air",B7="Laid in Duct"),Free_Air_Multi,EmptyCell)
)

To short this formula, use shorter names for ranges. Also create Names for comparisons:

B7LDG=OR(B7="Laid in Ground",B7="Laid Direct in Ground")
B7AOD=OR(B7="Laid in Free Air",B7="Laid in Duct")

=IF(B7LDG,GndInst,IF(B5="Single_Core",IF(B8="Trefoil",FreSnglAirTrefoil,IF(B8="Flat",FreAirSnglFlat,EmptyCell)),IF(B7AOD,FreAirMult,EmptyCell))

UPD

To let Names work on any sheet copied from the original one, define their scope for the worksheet:

How it works when "Sheet1 (2)" is copied from Sheet1:

edited Oct 18, 2024 at 18:32

answered Oct 15, 2024 at 18:04

rotabor

5,6072 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ahm2025 Over a year ago

i tried all these solutions but it always give me error: A named range you specified cannot be found, you can see this also:

Function InstallationType(B5 As String, B7 As String, B8 As String) As Variant     If B5 = "Single_Core" And B7 = "Laid in Free Air" And B8 = "Trefoil" Then         InstallationType = ThisWorkbook.Sheets("Data List").Range("I4").Value     ElseIf B5 = "Single_Core" And B7 = "Laid in Free Air" And B8 = "Flat" Then         InstallationType = ThisWorkbook.Sheets("Data List").Range("I3").Value  and so on     End If End Function

Ahm2025 Over a year ago

the modified answer works well for a small code but when i copy and paste it in the source , i can not paste it at all , i think the source is limited ,it works well after i delete some parts from the code and paste it in the source but it works with a wrong logic

rotabor Over a year ago

Can you state your initial task instead of your solution with the formula? It can happen the initial task can be solved in another way.

Ahm2025 Over a year ago

i actually named all ranges but i can not write all the code in the source of data validation as it is limited ,so i tried to use VBA function and it is still not works also

Ahm2025 Over a year ago

there is a problem after i duplicate the Model sheet the code in the source validation in the new sheet does not work well as the formulas in the (Data List) sheet always refer to the original sheet (Model) and does not refer to the duplicated sheets !!!!!

Collectives™ on Stack Overflow

Dependent dropdown list in Excel ,Data Validation and VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related