Dynamic dependent drop down list using an array formula

[mumps]

I have successfully created a dynamic dependent drop down list. I have used this code to insert the drop down into a range in "Sheet1".

With desWS.Range("F2:F" & lRow).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=Categories"
    End With

"Categories" refers to a named range: =Type[#Headers] which are the headers in a table in sheet "Lists" - A1:B1 which will be the items in the drop down in F2 of "Sheet1".
The code works properly. However, the following code generates the error "Application defined or object defined error":

With desWS.Range("G2").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=CatList"
    End With

"CatList" refers to a named range: =OFFSET(Lists!$A$1, 1, MATCH($F$2, Lists!$A$1:$B$1,0)-1, COUNTA(OFFSET(Lists!$A$1, 1, MATCH($F$2, Lists!$A$1:$B$1,0)-1, 20, 1)),1)
This formula should create the items in G2 of "Sheet1"from column A or B in "Lists" depending on the selection in F2 of "Sheet1". It allows the drop down in G2 to be dynamic to include any changes in column A or B in "Lists". I believe that this is an array formula and that is why it is generating the error. Would anyone have any suggestions on how to solve this problem?

 

[mumps]

Additionally, I entered this formula manually in G2 and it worked properly:

=OFFSET(Lists!$A$1, 1, MATCH($F2, Lists!$A$1:$B$1,0)-1, COUNTA(OFFSET(Lists!$A$1, 1, MATCH($F2, Lists!$A$1:$B$1,0)-1, 20, 1)),1)

Using this macro, it generated the error:

Sub Test()
    With Range("G2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:= _
            "=OFFSET(Lists!$A$1, 1, MATCH($F2, Lists!$A$1:$B$1,0)-1, COUNTA(OFFSET(Lists!$A$1, 1, MATCH($F2, Lists!$A$1:$B$1,0)-1, 20, 1)),1)"
    End With
End Sub

 

[mumps]

Also posted at: Dynamic dependent drop down list using formula