I have downloaded a forum for VBA which lets you search in a drop-down list. It works great until it encounters a dependent drop-down list(a drop-down that looks at an Indirect function.) It only displays the function and not the referred cell.

As I am quite new to VBA I had some troubles with adding the indirect function. There is a youtube video of the guy who wrote the code(https://www.youtube.com/watch?v=HXCwvRzRJZc) and I have tried to look over it and create another condition where it shows the value of the indirect drop-down list. But unfortunately I have failed numerous times. I understand that the Range and the validation of an active cell plays a part of it I just cannot puzzle it together.

The whole forum code as well as the range validation.

Sub Refresh_List()

Dim arr() As String
Dim i As Integer
Me.ListBox1.Clear
Dim rng As Range
Dim cel As Range

If IsValidation(ActiveCell) = True Then


If Valid_Range(ActiveCell.Validation.Formula1) = True Then
    Set rng = Range(Replace(ActiveCell.Validation.Formula1, "=", ""))


    For Each cel In rng
            If Me.TextBox1.Value = "" Then
            Me.ListBox1.AddItem cel.Value
        Else
        
            If VBA.InStr(UCase(cel.Value), UCase(Me.TextBox1.Value)) > 0 Then
                Me.ListBox1.AddItem cel.Value
            End If
        End If
Next
    

Else



arr = VBA.Split(ActiveCell.Validation.Formula1, ",")

For i = LBound(arr) To UBound(arr)
        If Me.TextBox1.Value = "" Then
            Me.ListBox1.AddItem arr(i)
        Else
            If VBA.InStr(UCase(arr(i)), UCase(Me.TextBox1.Value)) > 0 Then
                Me.ListBox1.AddItem arr(i)
            End If
        End If
Next i
End If
End If
On Error Resume Next
Me.ListBox1.ListIndex = 0
On Error GoTo 0

End Sub

Private Sub CommandButton1_Click()
    Me.CommandButton2.Visible = True
    Me.CommandButton1.Visible = False
    Me.Height = 256
    
End Sub

Private Sub CommandButton2_Click()
    Me.CommandButton1.Visible = True
    Me.CommandButton2.Visible = False
    Me.Height = 164

End Sub


Private Sub ListBox1_Click()

End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Call Update_Data

End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Then
        Call Update_Data
        End If
End Sub


Private Sub TextBox1_Change()
Call Refresh_List
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = 13 Then
        Call Update_Data
        End If
        

End Sub

Private Sub UserForm_Activate()
Call Refresh_List
End Sub

Sub Refresh_List()

Dim arr() As String
Dim i As Integer
Me.ListBox1.Clear
Dim rng As Range
Dim cel As Range

If IsValidation(ActiveCell) = True Then


If Valid_Range(ActiveCell.Validation.Formula1) = True Then
    Set rng = Range(Replace(ActiveCell.Validation.Formula1, "=", ""))


    For Each cel In rng
            If Me.TextBox1.Value = "" Then
            Me.ListBox1.AddItem cel.Value
        Else
        
            If VBA.InStr(UCase(cel.Value), UCase(Me.TextBox1.Value)) > 0 Then
                Me.ListBox1.AddItem cel.Value
            End If
        End If
Next
    

Else



arr = VBA.Split(ActiveCell.Validation.Formula1, ",")

For i = LBound(arr) To UBound(arr)
        If Me.TextBox1.Value = "" Then
            Me.ListBox1.AddItem arr(i)
        Else
            If VBA.InStr(UCase(arr(i)), UCase(Me.TextBox1.Value)) > 0 Then
                Me.ListBox1.AddItem arr(i)
            End If
        End If
Next i
End If
End If
On Error Resume Next
Me.ListBox1.ListIndex = 0
On Error GoTo 0

End Sub

Function Valid_Range(str As String) As Boolean
Dim rng As Range

On Error Resume Next
Set rng = Range(Replace(str, "=", ""))
On Error GoTo 0

If rng Is Nothing Then
    Valid_Range = False
Else
    Valid_Range = True
End If



End Function



Function IsValidation(rng As Range) As Boolean

Dim dvtype As Integer

On Error Resume Next
dvtype = rng.Validation.Type
On Error GoTo 0

If dvtype = 3 Then

    IsValidation = True
    
Else

    IsValidation = False

End If


End Function



Sub Update_Data()

ActiveCell.Value = Me.ListBox1.Value
If Me.OptionButton1.Value = True Then
    ActiveCell.Offset(1, 0).Select
    
    If Me.TextBox1.Value <> "" Then
        Me.TextBox1.Value = ""
    Else
        Call Refresh_List
    End If
    
    If Me.ListBox1.ListCount = 0 Then Me.Hide
    
    
End If

If Me.OptionButton2.Value = True Then
    ActiveCell.Offset(0, 1).Select
    
    If Me.TextBox1.Value <> "" Then
        Me.TextBox1.Value = ""
    Else
        Call Refresh_List
    End If
    
    If Me.ListBox1.ListCount = 0 Then Me.Hide
    
    
    
End If

If Me.OptionButton3.Value = True Then
     Me.TextBox1.Value = ""
    
    
End If

If Me.OptionButton4.Value = True Then
     Me.TextBox1.Value = ""
     Unload Me
    
End If

End Sub

Private Sub CommandButton3_Click()
    Me.TextBox1.Value = ""
    Unload Me
End Sub