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

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

Hi Ajish,

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

Code:

---

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

---

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

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

Hi Ajish,

May be..

Code:

---

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.

how can i attach a file here????

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

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

Hi

Try

Code:

---

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

---

Quote:

---

Originally Posted by Admin

Hi

Try

Code:

---

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

Quote:

---

Originally Posted by Admin

Hi

Try

Code:

---

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

---

---

Code:

---

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, Worksheets("PMast").Range("A:A"), 0)
    If Not IsError(LookupValueRow) Then
      Worksheets("PMast").Cells(LookupValueRow, "BL") = Range("D32").Value
    End If
  End If
End Sub

---

hi this code is working perfect
the code was modified and given to me by sir Rick
thanks to u Admin for taking effort n time to help me
i know i could not convey what i needed properly due to language problem