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",K22="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)<4500,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

[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


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