Using IF AND statement to return row numbers

[Kobot]

Hi all,

I need some help figuring out how to pick up data from one tab within an excel workbook and transposing some of that data onto another tab.

Basically, I have a list of names, addresses, and monetary values with a drop down list of answers (yes or no). If the answer is yes I want specific data from that row to copy over to another sheet.

I've done my best to illustrate the data below.

Workbook #1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Row #[/TD]
[TD]Name[/TD]
[TD]Value of contribution[/TD]
[TD]Is this contribution from a fundraising event? (yes or no)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Charlie[/TD]
[TD]$200[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Sam[/TD]
[TD]$27[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John[/TD]
[TD]$1,000[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Christina[/TD]
[TD]$300[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Helen[/TD]
[TD]$100[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Workbook #2 (Data I want to extract)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Row #[/TD]
[TD]Value of contribution[/TD]
[TD]Answer [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]$200[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]$300[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

I want the second workbook to eliminate any "no" rows and only populate "yes" rows.

How do I do this?

Please help.

 

[mumps]

Are you using two separate workbooks or two separate sheets in the same workbook? Do you want to copy over the data for the row automatically when you select "Yes"? What are the names of the source sheet and destination sheet? In which columns are the contributions listed and in which column is the drop down list?

 

[Kobot]

Are you using two separate workbooks or two separate sheets in the same workbook?
I am using one workbook with two separate sheets.

Do you want to copy over the data for the row automatically when you select "Yes"?
Partially yes, I want to copy over some data from the row automatically when I select "yes".
I only want to copy the Amount (column I) and the Contributor number (column A) from the source sheet
I want to put Column I (from source sheet) into Column D (on destination sheet) and Column A (from source sheet) into Column E (on destination sheet)

What are the names of the source sheet and destination sheet?
The name of the source sheet is Individual Contrib. & Funds.
The name of the destination sheet is Fundraising Events & Activities

In which columns are the contributions listed and in which column is the drop down list?
The contributions are listed in column I
The drop down list is in Column N

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Individual Contrib. & Funds" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Please note that the "Yes" (in red) begins with a capital letter. The "Yes" in your drop down list must also begin with a capital letter. Close the code window to return to your sheet. Make a selection in column N.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N:N")) Is Nothing Then Exit Sub
    Dim desWS As Worksheet
    Set desWS = Sheets("Fundraising Events & Activities")
    If Target = "[COLOR="#FF0000"]Yes[/COLOR]" Then
        desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1, 0) = Cells(Target.Row, 9)
        desWS.Cells(desWS.Rows.Count, "E").End(xlUp).Offset(1, 0) = Cells(Target.Row, 1)
    End If
End Sub

 

[Kobot]

Hi mumps,

We are on the right track, I entered the code and it works... just not the way I need it to.

The amount and contributor number are copying over to the Fundraiser tab but they are copying over onto two separate rows.

I need them to copy onto the same row.

Can you please help me move the amount and and contributor number onto the same row?

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N:N")) Is Nothing Then Exit Sub
    Dim desWS As Worksheet, LastRow As Long
    Set desWS = Sheets("Fundraising Events & Activities")
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    If Target = "Yes" Then
        desWS.Cells(LastRow, "D") = Cells(Target.Row, 9)
        desWS.Cells(LastRow, "E") = Cells(Target.Row, 1)
    End If
End Sub

 

[Kobot]

Hi mumps,

This is great, thank you.

I tested it out and it works. I did notice that if I change the answer "Yes" to "No" the line item still remains on the fundraising sheet.

Is there a way to remove that row if the answer changes from a Yes to a No?

 

[mumps]

In order to do that, each row would need to have a unique identifier such as an ID number. You said that you have names and addresses. If the names and/or addresses are unique, we could use one of those items. We would have to copy that item to the "Fundraising Events & Activities" sheet as well. What columns are the names and addresses in?

 

[Kobot]

"Contributor Last name" is in column C on the source sheet. It can be transposed into Column B in the destination sheet.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N:N")) Is Nothing Then Exit Sub
    Dim desWS As Worksheet, LastRow As Long, lName As Range
    Set desWS = Sheets("Fundraising Events & Activities")
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    If Target = "Yes" Then
        With desWS
            .Cells(LastRow, "D") = Cells(Target.Row, 9)
            .Cells(LastRow, "E") = Cells(Target.Row, 1)
            .Cells(LastRow, "B") = Cells(Target.Row, 3)
        End With
    Else
        Set lName = desWS.Range("B:B").Find(Cells(Target.Row, 3))
        desWS.Rows(lName.Row).EntireRow.Delete
    End If
End Sub