Cell value based on another cell if text in there's specific text in another cell

[carlomscalisi]

Hopefully the title wasn't too confusing.

I have a workbook with two sheets. On one sheet I have a column for comments and an adjacent column with a list value of "Y" or "N". Then I have another sheet and on that sheet I would like to have a column that contains the comments from the other sheet IF the adjacent column contains "Y". I hope that makes sense. Please help!


Thank you!

 

[mumps]

In which columns are the comments and Y/N's?

 

[DanteAmor]

Try with this macro.

Change the data in red for your real information.

Sub CopyComment()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim c As Range, cmt As Comment
    
    Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
    col = "[COLOR=#ff0000]C[/COLOR]"   'column with list value "Y"/"N"
    des = "[COLOR=#ff0000]A[/COLOR]"   'destination column
    
    For Each c In sh1.Range(col & "1", sh1.Range(col & Rows.Count).End(xlUp))
        If c.Value = "[COLOR=#ff0000]Y[/COLOR]" Then
            Set cmt = c.Offset(0, -1).Comment
            If Not cmt Is Nothing Then
                sh2.Range(des & sh2.Range(des & Rows.Count).End(xlUp).Offset(1).Row) = cmt.Text
            End If
        End If
    Next
    MsgBox "End"
End Sub

 

[carlomscalisi]

I'm going to attach a couple pictures that will hopefully help.


On Sheet 3-21, if Column M contains "Y" in any Row, I would like the text from the corresponding Row under Column A ("Patient") to be copied to Sheet Follow-Up under Column A ("Patient"). Similarly, if Column M on Sheet 3-21 contains "Y" in any Row, I would like the text from the corresponding Row under Column L ("Comments") to be copied to Sheet Follow-Up under Column B ("Follow-up Item). And I would like to be able to change this weekly to use a different sheet for reference.


Explanation: I use this spreadsheet during a weekly meeting and use each Sheet, dated appropriately, to document items that need follow-up. I would like to have the follow-up items from that sheet, if marked with "Y" in the "F/u" Column, to automatically pull over into the Follow-Up Sheet for easy reference and further organization.


Again, I truly appreciate any help. This is way beyond my Excel knowledge and I wasn't even sure how to look it up online!

 

[carlomscalisi]

It's not allowing me to upload the pictures. Here are the links:

https://ibb.co/wcg14SQ

https://ibb.co/9Gq7xcF

 

[mumps]

Try this macro. It will prompt you to enter the name of the reference sheet.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, response As String
    response = InputBox("Please enter the name of the reference sheet.")
    If response = "" Then Exit Sub
    Set srcWS = Sheets(response)
    Set desWS = Sheets("Follow-up")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS
        .Range("M1:M" & LastRow).AutoFilter Field:=1, Criteria1:="Y"
        .Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range("L2:L" & LastRow).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        .Range("M1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Run the following code about the week you need. The result will remain on the sheet "Follow-up"

Sub CopyComment()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim c As Range, lr As Long
    
    Set sh1 = ActiveSheet
    Set sh2 = Sheets("Follow-up")
    sh2.Range("A2:B" & Rows.Count).ClearContents
    
    col = "M"   'column with list value "Y"/"N"
    lr = 2
    For Each c In sh1.Range(col & "3", sh1.Range(col & Rows.Count).End(xlUp))
        If c.Value = "Y" Then
            sh2.Cells(lr, "A").Value = sh1.Cells(c.Row, "A").Value
            sh2.Cells(lr, "B").Value = sh1.Cells(c.Row, "L").Value
            lr = lr + 1
        End If
    Next
    MsgBox "End"
End Sub

 

[carlomscalisi]

This worked great. I only had to change A2 to A3 and L2 to L3, to avoid referencing the header row. Follow-up question: Is it possible to only copy the values, not the formatting?

 

[carlomscalisi]

Thank you, Dante!

 

[DanteAmor]

Thank you, Dante!

I'm glad to help you. Thanks for the feedback.