Dependent dropdown list in Excel ,Data Validation and VBA

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:

Solution

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: