Problem using formulas within macro + problem with defined name.

Hi
Im having 2 problems with this following formula

=IF(ISNUMBER(SEARCH("qn",INDEX(_Data,0,19))),"Liisa - QN check",
IF(ISNUMBER(SEARCH("conformance",INDEX(_Data,0,19))),"Liisa - to check conformance centre",
IF(INDEX(_Data,0,18)>0,"Liisa - stock check",
IF(AND(INDEX(_Data,0,17)>($Z$1+7),INDEX(_Data,0,17)<>""),"Pull forward",
IF(AND(INDEX(_Data,0,17)<($Z$1-3),INDEX(_Data,0,17)<>""),"Chase promise date",
IF(AND(INDEX(_Data,0,17)<=($Z$1+7),INDEX(_Data,0,17)>($Z$1),INDEX(_Data,0,17)<>""),"Is being delivered in less than 7 days",
IF(AND($Q2<=($Z$1),$Q2>=($Z$1-3),$Q2<>""),"Liisa to check, may have been delivered, may not","no info")))))))

First problem is that for some reason it wont let me use defined name for the last IF statement. At the moment its like this

IF(AND($Q2<=($Z$1),$Q2>=($Z$1-3),$Q2<>""),"Liisa to check, may have been delivered, may not","no info")))))))

But it should look loke this.

IF(AND(INDEX(_Data,0,17)<=($Z$1),INDEX(_Data,0,17)>=($Z$1-3),INDEX(_Data,0,17)<>""),"Liisa to check, may have been delivered, may not","no info")))))))

So all in all it would look like this:

=IF(ISNUMBER(SEARCH("qn",INDEX(_Data,0,19))),"Liisa - QN check",
IF(ISNUMBER(SEARCH("conformance",INDEX(_Data,0,19))),"Liisa - to check conformance centre",
IF(INDEX(_Data,0,18)>0,"Liisa - stock check",
IF(AND(INDEX(_Data,0,17)>($Z$1+7),INDEX(_Data,0,17)<>""),"Pull forward",
IF(AND(INDEX(_Data,0,17)<($Z$1-3),INDEX(_Data,0,17)<>""),"Chase promise date",
IF(AND(INDEX(_Data,0,17)<=($Z$1+7),INDEX(_Data,0,17)>($Z$1),INDEX(_Data,0,17)<>""),"Is being delivered in less than 7 days",

IF(AND(INDEX(_Data,0,17)<=($Z$1),INDEX(_Data,0,17)>=($Z$1-3),INDEX(_Data,0,17)<>""),"Liisa to check, may have been delivered, may not","no info")))))))

Second problem is that Im trying to use macro to populate this formula in column AM. So far I have not had any success. I have tried the 2 following macros with no luck.

Sub TEST_formulas2()
'formula
    Range("AM2").Select
    ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""qn"",INDEX(_Data,0,19))),""Liisa - QN check"",IF(ISNUMBER(SEARCH(""conformance"",INDEX(_Data,0,19))),""Liisa - to check conformance centre"",IF(INDEX(_Data,0,18)>0,""Liisa - stock check"",IF(AND(INDEX(_Data,0,17)>($Z$1+7),INDEX(_Data,0,17)<>""""),""Pull forward"",IF(AND(INDEX(_Data,0,17)<($Z$1-3),INDEX(_Data,0,17)<>""""),""Chase promise date"",IF(AND(INDEX(_Data,0,17)<=($Z$1+7),INDEX(_Data,0,17)>($Z$1),INDEX(_Data,0,17)<>""""),""Is being delivered in less than 7 days"",IF(AND($Q3<=($Z$1),$Q3>=($Z$1-3),$Q3<>""""),""Liisa to check, may have been delivered, may not"",""no info"")))))))"

    Range("AM2").Select
    Dim K8 As Long
        K8 = Cells(Rows.Count, "A").End(xlUp).Row
        Selection.AutoFill Destination:=Range("AM2:AM" & K8)

End Sub

and

Sub TEST_formulas3()

'Formula
    With Sheets("New evaluation")
        With .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Offset(, 38)
            .FormulaLocal = "=IF(ISNUMBER(SEARCH(""qn"",INDEX(_Data,0,19))),""Liisa - QN check""," & _
                                    "IF(ISNUMBER(SEARCH(""conformance"",INDEX(_Data,0,19))),""Liisa - to check conformance centre""," & _
                                    "IF(INDEX(_Data,0,18)>0,""Liisa - stock check""," & _
                                    "IF(AND(INDEX(_Data,0,17)>($Z$1+7),INDEX(_Data,0,17)<>""""),""Pull forward""," & _
                                    "IF(AND(INDEX(_Data,0,17)<($Z$1-3),INDEX(_Data,0,17)<>""""),""Chase promise date""," & _
                                    "IF(AND(INDEX(_Data,0,17)<=($Z$1+7),INDEX(_Data,0,17)>($Z$1),INDEX(_Data,0,17)<>""""),""Is being delivered in less than 7 days""," & _
                                    "IF(AND($Q3<=($Z$1),$Q3>=($Z$1-3),$Q3<>""""),""Liisa to check, may have been delivered, may not"",""no info"")" & _
                                                    ")" & _
                                                ")" & _
                                            ")" & _
                                        ")" & _
                                    ")" & _
                                ")"
                                
        
        End With
        End With
        
End Sub

I have attached the xls ofcourse. Any help woud be greatly appreciated.