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.

 

[Kobot]

Thank you!

This works perfectly.

Now I've run into another road block.

I set up the destination sheet with several formulas and conditions that no longer work because of the automated lines from the source sheet.

I wanted the automated lines to appear within a table I created but they only appear underneath the table on the destination sheet.

So I tried to remove the table and leave all the formulas and conditions but the contributions are still appearing below the lines.

Is is possible to automate all of the columns within the formula you created above the specifics below?

I need to have a drop down list in Column A "Revenue type" on the destination sheet "Fundraising Events & Activities".

The drop down list needs to have three options "Ticket revenue, Other revenue deemed a contribution, Other revenue not deemed a contribution".

In Column C "Details", this column needs to turn red if "Other revenue deemed a contribution or Other revenue not deemed a contribution" is selected in Column A and a note should appear when hovering over the column "If cell is red, please provide further details".

In Column G "Total" I need the following formula: =D9*F9

In Column H "Total Ticket Revenue" I need the following formula: =IF(AND(D9>=25,A9="Ticket revenue"),D9,0)

In Column I "Other revenue deemed a contribution" I need the following formula: =IF(AND(D9>=25,A9="Other revenue deemed a contribution"),D9,0)

In Column J "Contribution of $25 or less (not deemed a contribution)" I need the following formula: =IF(D9<=25,D9,0)

I know it's a lot to ask and I'm hopeful you can help me, you've been a wizard so far!

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Kobot]

https://www.dropbox.com/s/wnohs6f5vxm1d9t/2018 Campaign Tracking Workbook - Maco Enabled.xlsm?dl=0

 

[mumps]

Place this macro in the code module for sheet "Individual Contrib. & Funds":

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N:N")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim desWS As Worksheet, lastRow As Long, lName As Range, bottomB As Long
    Set desWS = Sheets("Fundraising Events & Activities")
    lastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    bottomB = desWS.Range("B" & desWS.Rows.Count).End(xlUp).Row + 1
    If Target = "Yes" Then
        With desWS
            .Cells(bottomB, "D") = Cells(Target.Row, 9)
            .Cells(bottomB, "E") = Cells(Target.Row, 1)
            .Cells(bottomB, "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
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Place this macro in the code module for sheet "Fundraising Events & Activities":

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Other revenue deemed a contribution", "Other revenue not deemed a contribution"
            Cells(Target.Row, 3).Interior.ColorIndex = 3
    End Select
End Sub

 

[mumps]

Please use this version of the second macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Other revenue deemed a contribution", "Other revenue not deemed a contribution"
            Cells(Target.Row, 3).Interior.ColorIndex = 3
        Case Else
            Cells(Target.Row, 3).Interior.ColorIndex = xlNone
    End Select
End Sub