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

[ajish002]

D32 has the formula
=IF(AND(VLOOKUP(J1,PMast!A8:CC5001,9,FALSE)="P",K2 2="NA"),"NA",IF(IF(AND(D30="NA",D31="NA"),"NA",IF( MAX(D30,D31)<4500,4500,MAX(D30,D31)))>17950,17950, IF(AND(D30="NA",D31="NA"),"NA",IF(MAX(D30,D31)<450 0,4500,MAX(D30,D31)))))

D32 changes with the vlookup values entered therein
what i need is

each time when i enter a new lookupvalue i need the D32 to be
copied/written permanently, in the BL column of corresponding row of vlookup value in the the sheet PMast

that is
if vlook up value is 1 and d32=2526 then in sheet PMast, BL1 = 2526

& when i enter vlook up value 6 and d32=5255 then in sheet PMast, BL6= 5255 and BL1 = 2526

again if i enter vlook up value is 1 and d32=3333(since i changed some other values) then in sheet PMast,
BL1 = 3333 (re-written) and BL6=5255

[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

[ajish002]

Thanks in advance
but the value is being pasted in the BL column 2nd row, if the vlookup value is given "2"
the value need to be pasted in BLth column,but not in 2nd row if the vlookup value is "2",
but corresponding lookup value is in the A column and if lookup value "2" is in A8 then D32 should be written in BL8

[ajish002]

Thanks in advance
but the value is being pasted in the BL column 2nd row, if the vlookup value is given "2"
the value need to be pasted in BLth column,but not in 2nd row if the vlookup value is "2",
but corresponding lookup value is in the A column and if lookup value "2" is in A8 then D32 should be written in BL8

[Admin]

Hi Ajish,

May be..

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address(0, 0) <> "J1" Then Exit Sub
    
    Dim LookupValueRow  As Variant
    
    If Len(Target) Then
        LookupValueRow = WorksheetFunction.Match(Target, Range("a:a"), 0)
        If Not IsError(LookupValueRow) Then
            Worksheets("PMast").Cells(LookupValueRow, "BL") = Range("D32").Value
        End If
    End If
        
End Sub

If this is not the case, attach a workbook with expected result.

[ajish002]

how can i attach a file here????

[Excel Fox]

Click on Go Advanced, and scroll down to find the attachment button

[ajish002]

the code give works almost fine but doesnt actually write in the exact row of (lookup value columnA)
here in sample sheet PMast is named as sheet2
also note if there id no such value in column A for typed look up value it should not write any thing

[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

[ajish002]

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

again it writes in sheet2 column BL 2nd row(BL2) when lookupvalue (ie J1) is given "2"
but since "2"(J1) is in columnA row3 in sheet2 output should be in BL3