Import multiple dynamic arrays with formulas - Excel

Question

My objective is to find a way to import a dynamic arrays in each row which will be used to populate a data validation list. Each array which will be filtered by cell value (for each row will be different) for a series of rows, but avoid overlapping?

Any ideas will be greathly appriciated.

Answer 1

This is a different approach than what you describe you want, but I think it's more flexible.

1. Put this code in a regular module:

'A function to return a range containing the
'  various values which need to appear in the validation list
Public Function ListCompile() As Range

    Dim c As Range, arr, v, ws As Worksheet, vRange As Range
    
    On Error Resume Next
    Set c = Application.Caller 'get the cell which is calling this function via the DV list
    On Error GoTo 0
    If c Is Nothing Then Exit Function  'exit if cell not available
    
    Set ws = c.Parent                  'the worksheet with the DV list
    v = c.Offset(0, -1).Value          'the value we're looking up
    If Len(v) = 0 Then Exit Function   'nothing to look up
    
    'evaluate the lookup function using the entered value
    arr = Application.Evaluate("=SORT(UNIQUE(FILTER(tblClients[Clients],ISNUMBER(SEARCH(""" & _
                               v & """,tblClients[Clients])),""Not Found"")))")
    
    With ws.Range("M4")          'can be anywhere on any worksheet
        .Resize(100).Value = ""  'clear any  previous list
        Set vRange = .Resize(UBound(arr, 1), 1) 'range to get the evaluated list
    End With
    vRange.Value = arr         'populate the choices
    Set ListCompile = vRange   'return the range to the validation list named range "tester"
End Function

2. Define a named range "tester" with "RefersTo" equal to ListCompile()

3. Finally set your data validation list range to: =tester

Whenever you click on one of those DV cells, a call gets made via tester to ListCompile(); within that method we can reference the specific cell which was clicked, using Application.Caller. With that information we can build a list of values for the drop-down list and place that list in a range which is returned from ListCompile() - that range will be used by the drop-down list.

Here's what my demo worksheet looks like: