Hi Buckshot,

I would suggest that you need to be able to enter the regular offerings quickly and easily first and then later let Excel match them up to the Names and Addresses and format them as you have displayed.

Initially you would need to enter Date, ID, Amount and Check# as a list. Once these are all entered then you can match them up to the Names and Addresses and use possibly a pivot table to have all your data sorted with a separate page for each ID (person/family).

Also there are probably lots of software packages around which you can buy which will do what you want and with heaps of reporting and letter preparation. I hate re-inventing the wheel!

Good Luck!

Peter Moran

I would setup data entry in one (data) sheet (you will have many “duplicate IDs”)
columns like ID#, date, check#

Another sheet (info sheet) with ID#, name, address, etc (1 row per ID, ca 300 rows)

You can use a a pivot table to get a summary table of info – (I would put this on a third (summary) sheet, or if it has to be in that particular output format, formulas might work or even a macro.

I would envision the output have some combobox to select an individual and “extract” the info from the data sheet and the info sheet and put it into the summary and also a macro to print all the reports: it would loop thru all the names, selecting each, (which extracts the info), and prints the report.

Hope this helps, post back if you need more info.

Steve

I would setup data entry in one (data) sheet (you will have many “duplicate IDs”)
columns like ID#, date, check#

Another sheet (info sheet) with ID#, name, address, etc (1 row per ID, ca 300 rows)

You can use a a pivot table to get a summary table of info – (I would put this on a third (summary) sheet, or if it has to be in that particular output format, formulas might work or even a macro.

I would envision the output have some combobox to select an individual and “extract” the info from the data sheet and the info sheet and put it into the summary and also a macro to print all the reports: it would loop thru all the names, selecting each, (which extracts the info), and prints the report.

Hope this helps, post back if you need more info.

Steve

Hey Buckshot;

I read your post a few days back and was intrigued with trying to figure out a solution. I like to think of Woody’s Lounge as a place to get the best “living brain teasers”. I admit that I have only recently starting learning VBA for Excel so most of my work is with formulas and the attached file only uses macros created by the macro recorder.
The spreadsheet I created stores the information of the donatees and then a pull-down box (name) selects the person and all pertinent information. This includes all donations, dates, special gifts, totals, etc..
The Data Entry button just links to the start of the data entry area (R69). Scroll down to enter all new data. The Re-Sort button is to use after you enter additional information. It will re-sort the data and look for any new names which will then be added to the pull down list.
The print area is set to only print one page which is the information for each person. There is room for 52 entries for the year. C8 counts how many donations are given and will turn red if more than 52 are found. Most of the formulas are array formulas so any editing will need to be finished with ctrl – shift – enter. I originally tried to set-up the sheet exactly like you posted but found problems with the layout so I did what I thought would be the most appropriate.
I don’t know if this is what you are looking for or if this file can be usefull but I hope it helps. If you wish more information on the file or assistance adjusting it, let me know.

yoyoPHIL

Hey Buckshot;

I read your post a few days back and was intrigued with trying to figure out a solution. I like to think of Woody’s Lounge as a place to get the best “living brain teasers”. I admit that I have only recently starting learning VBA for Excel so most of my work is with formulas and the attached file only uses macros created by the macro recorder.
The spreadsheet I created stores the information of the donatees and then a pull-down box (name) selects the person and all pertinent information. This includes all donations, dates, special gifts, totals, etc..
The Data Entry button just links to the start of the data entry area (R69). Scroll down to enter all new data. The Re-Sort button is to use after you enter additional information. It will re-sort the data and look for any new names which will then be added to the pull down list.
The print area is set to only print one page which is the information for each person. There is room for 52 entries for the year. C8 counts how many donations are given and will turn red if more than 52 are found. Most of the formulas are array formulas so any editing will need to be finished with ctrl – shift – enter. I originally tried to set-up the sheet exactly like you posted but found problems with the layout so I did what I thought would be the most appropriate.
I don’t know if this is what you are looking for or if this file can be usefull but I hope it helps. If you wish more information on the file or assistance adjusting it, let me know.

yoyoPHIL

Here’s my stab at something. You add new donations to the bottom of the Donations sheet. You type into the yellow cells to set your report parameters.

There are some range names and data validations and there’s code in the Donations worksheet:

Option Explicit
Private Sub Worksheet_Activate()
    Dim Col As Integer
    On Error GoTo TurnOnFilter
    Col = ActiveSheet.AutoFilter.Range.Column
    On Error GoTo 0
    Exit Sub
TurnOnFilter:
    If Err.Number = 91 Then
        Range("Dates").AutoFilter
        FilterDates
        FilterIDs
    End If
    Resume
End Sub

Private Sub Worksheet_Change(ByVal ChangedCell As Range)
    If Not Intersect(ChangedCell, Range("Quarter")) Is Nothing Or _
       Not Intersect(ChangedCell, Range("Year")) Is Nothing Then
        FilterDates
    Else
        If Not Intersect(ChangedCell, Range("ID")) Is Nothing Then
            FilterIDs
        End If
    End If
End Sub

Private Sub FilterDates()
    With Range("Dates")
        .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1, _
        Criteria1:=">=" + Format(Range("StartDate").Value, "m/d/yyyy"), _
        Operator:=xlAnd, _
        Criteria2:="<=" + Format(Range("EndDate").Value, "m/d/yyyy")
    End With
End Sub

Private Sub FilterIDs()
    With Range("IDs")
        If IsEmpty(Range("ID")) Or Not IsNumeric(Range("ID")) Or Range("ID") = 0 Then
            .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1
        Else
            .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1, _
            Criteria1:="=" + Format(Range("ID").Value, "#")
        End If
    End With
End Sub

It would be a fairly simple matter to write a macro that would cycle through the Donor list and print a page for each one. If you need help with that piece, just post back here.

Here’s my stab at something. You add new donations to the bottom of the Donations sheet. You type into the yellow cells to set your report parameters.

There are some range names and data validations and there’s code in the Donations worksheet:

Option Explicit
Private Sub Worksheet_Activate()
    Dim Col As Integer
    On Error GoTo TurnOnFilter
    Col = ActiveSheet.AutoFilter.Range.Column
    On Error GoTo 0
    Exit Sub
TurnOnFilter:
    If Err.Number = 91 Then
        Range("Dates").AutoFilter
        FilterDates
        FilterIDs
    End If
    Resume
End Sub

Private Sub Worksheet_Change(ByVal ChangedCell As Range)
    If Not Intersect(ChangedCell, Range("Quarter")) Is Nothing Or _
       Not Intersect(ChangedCell, Range("Year")) Is Nothing Then
        FilterDates
    Else
        If Not Intersect(ChangedCell, Range("ID")) Is Nothing Then
            FilterIDs
        End If
    End If
End Sub

Private Sub FilterDates()
    With Range("Dates")
        .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1, _
        Criteria1:=">=" + Format(Range("StartDate").Value, "m/d/yyyy"), _
        Operator:=xlAnd, _
        Criteria2:="<=" + Format(Range("EndDate").Value, "m/d/yyyy")
    End With
End Sub

Private Sub FilterIDs()
    With Range("IDs")
        If IsEmpty(Range("ID")) Or Not IsNumeric(Range("ID")) Or Range("ID") = 0 Then
            .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1
        Else
            .AutoFilter Field:=.Column - .Parent.AutoFilter.Range.Column + 1, _
            Criteria1:="=" + Format(Range("ID").Value, "#")
        End If
    End With
End Sub

It would be a fairly simple matter to write a macro that would cycle through the Donor list and print a page for each one. If you need help with that piece, just post back here.