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

[Admin]

Hi Ajish,

Put this code in Sheet module. Right click on your formula tab > View code and paste the code there.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address(0, 0) <> "J1" Then Exit Sub
    
    If Len(Target) Then
        If IsNumeric(Target) Then
            With Worksheets("PMast").Range("BL:BL")
                .Cells(Target.Value, 1) = Range("D32").Value
            End With
        End If
    End If
        
End Sub