Conditional Inclusion of Text in a Column

[expos4ever]

This is the kind of thing I am trying to do:

1. I will enter the names of people in column A of Sheet 1.
2. In column B of Sheet 1, I will implement a checklist with two options - Yes and No
3. I will select Yes for each person whose name I want to have appear in another column-based list that I want to appear on Sheet 2. Of course, I want the list on Sheet 2 to not have any "holes" (empty cells).

Example (note: I can't figure out the alignment in what follows - but there are two columns in sheet 1):

Sheet 1:
A B
Fred Yes
Jane No
Julian No
Lucy Yes

Sheet 2
A
Fred
Lucy

Note how there are not blank cells for Jane and Julian.

Any suggestions welcomed.

 

[mumps]

I think the most efficient way to do this is as follows:
Create a data validation drop down list in column B of Sheet1 which includes "Yes" and "No".
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in the drop down in columns B and if you select "Yes", the name in column A will be automatically entered in Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    If Target = "Yes" Then
        Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Target.Offset(0, -1)
    End If
    Application.ScreenUpdating = True
End Sub

 

[expos4ever]

Thanks mumps. However, when I follow your instructions, the list of "selected" names does not appear on Sheet 2; instead, the selected names are appended to the list of names on Sheet1 with sheet 2 entirely unaffected (it remains completely blank).

Actually, I decided to reverse sheets 1 and 2, but I have been careful to account for this (I changed Sheet2 to Sheet1 in the macro, and I pasted the macro into Sheet 2). However, the preceding paragraph is written as though I had not reversed things. Hope that is not too confusing.

 

[mumps]

Click here to download a sample file so you can see how it works. Just select "Yes" in column B of Sheet1.

 

[expos4ever]

Click here to download a sample file so you can see how it works. Just select "Yes" in column B of Sheet1.

I found another way to achieve the desired functionality (using INDEX, ROW, and SMALL functions; however, I very much appreciate your assistance.

 

[mumps]

Glad it all worked out.