write permanently to another sheet from a cell with formulated lookup value

[Admin]

Hi

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address(0, 0) <> "J1" Then Exit Sub
    
    Dim LookupValueRow  As Variant
    
    Const DestSheet     As String = "Sheet2"    '<<<<< adjust to suit
    Const ColA_StartRow As Long = 5             '<<<<< adjust to suit
    
    If Len(Target) Then
        LookupValueRow = WorksheetFunction.Match(Target, Range("a:a"), 0)
        If Not IsError(LookupValueRow) Then
            Worksheets(DestSheet).Cells(LookupValueRow - ColA_StartRow + 1, "BL") = Range("D32").Value
        End If
    End If
        
End Sub