Using IF AND statement to return row numbers

Kobot

New Member
Joined
Dec 5, 2018
Messages
7
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
Row #NameValue of contributionIs this contribution from a fundraising event? (yes or no)
1Charlie$200Yes
2Sam$27No
3John$1,000No
4Christina$300Yes
5Helen$100No

<tbody>
</tbody>

Workbook #2 (Data I want to extract)
Row #Value of contributionAnswer
1$200Yes
4$300Yes

<tbody>
</tbody>

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

How do I do this?

Please help.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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?
 
Upvote 0
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
 
Upvote 0
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.
Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
Try:
Code:
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
"Contributor Last name" is in column C on the source sheet. It can be transposed into Column B in the destination sheet.
 
Upvote 0
Try:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top