2 Worksheets and Yes answer

[sh3rvin]

So I have 2 worksheets.

Sheet 1 contains 425 rows and the last column being used is O currently. The first column contains a unique identifier number.
Sheet 2 is used to send out mail merges it contains less rows until it is filled and the last column being used is S currently.

Now in Sheet1, in column O I will be entering the answer "yes" to indicate we have had a response with our mail merge. How do have this answer appear in Sheet2 in Column R using the first column on both sheets which is a unique identifier number?

 

[Trebor76]

Hi sh3rvin,

Put the following in cell R2 (assuming this is the starting Row - change the following to suit) and copy down as far as required:

=VLOOKUP(A2,Sheet1!A:O,15,FALSE)

Regards,

Robert

 

[My Aswer Is This]

Try this:
This script will run when you enter the value "Yes" into Column(O) on Sheet(1)

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet(1) tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/20/2019  7:48:44 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 15 Then
Dim ans As Long
Dim m As Long
If Target.Value = "Yes" Then
ans = Target.Row
m = Cells(ans, 1).Value
Dim SearchString As String
Dim SearchRange As Range
SearchString = m
Dim lastrow As Long
lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Sheets(2).Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Offset(0, 17).Value = "Yes"
End If
End If
End Sub

 

[sh3rvin]

Hi sh3rvin,

Put the following in cell R2 (assuming this is the starting Row - change the following to suit) and copy down as far as required:

=VLOOKUP(A2,Sheet1!A:O,15,FALSE)

Regards,

Robert

Thank you Robert, this has worked. Apologies for replying back now.

 

[sh3rvin]

Try this:
This script will run when you enter the value "Yes" into Column(O) on Sheet(1)

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet(1) tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/20/2019  7:48:44 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 15 Then
Dim ans As Long
Dim m As Long
If Target.Value = "Yes" Then
ans = Target.Row
m = Cells(ans, 1).Value
Dim SearchString As String
Dim SearchRange As Range
SearchString = m
Dim lastrow As Long
lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Sheets(2).Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Offset(0, 17).Value = "Yes"
End If
End If
End Sub

I could try this but for a different purpose. In the other tab, when I enter the filename of a letter I sent out, I then have to update the main sheet to "Yes" for letter sent out. Could something similar to this work? The problem is the filename will be unique for a batch of letters and starts with 00x -