Quarter Reports (2000)

Topic

New Reply

Hi we are trying to get all of our offerings on a spreadsheet. There are over 300 people to record.
They want Name~Address~ID#
1st quarter Jan-march, 2nd Quarter Apr-June, 3rd Quarter july-Sept and 4th Quarter Oct-Dec.
They document how much that person gave along with the check #. They have a place for special offerings. They have total per quarter. And a Grand Total per year. I

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

The one major thing to be concerned about with such a large number of array formulas that look at a large table of data is that there are a humungous number of calcs being done and the spreadsheet can get very sluggish. Any data entry requires all the calcs to be redone so it is constantly recalculating.

It would be prudent to use a pivot table (to avoid coding) or have a macro run (after the pulldown changes) to extract the info. With large datasets this will ultimately be a much faster solution than using the array formulas.

If you put the “datatable” on another sheet (recommended) you could even have the macro run whenever the datasheet was deactivated, so after leaving the datasheet (presumably modifying or adding new data) the summary would be up-to-date for the current selection.(you still would have it run after, a name was selected)

Just my ,

Steve

Reply | Quote

The one major thing to be concerned about with such a large number of array formulas that look at a large table of data is that there are a humungous number of calcs being done and the spreadsheet can get very sluggish. Any data entry requires all the calcs to be redone so it is constantly recalculating.

It would be prudent to use a pivot table (to avoid coding) or have a macro run (after the pulldown changes) to extract the info. With large datasets this will ultimately be a much faster solution than using the array formulas.

If you put the “datatable” on another sheet (recommended) you could even have the macro run whenever the datasheet was deactivated, so after leaving the datasheet (presumably modifying or adding new data) the summary would be up-to-date for the current selection.(you still would have it run after, a name was selected)

Just my ,

Steve

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks everyone for all your help. I

Reply | Quote

The result of a lookup “of a blank cell” always results in a zero. It is just like using something like:
=A1 will give a 0 if A1 is blank.

There are a couple workarounds: instead of using a blank cell in the ‘ChurchFunds” sheet, add a space to those cells, then a space will be “looked up” and it will display the space in your output.

Another means is to change the formula:
In your “amount column” use a variant of (change the lookup column as desired):

=IF(C3="","",VLOOKUP(C1,'5-392108-Churchtosend.xls'!total,7,FALSE))

and in your check num:
=IF(VLOOKUP(C1,’5-392108-Churchtosend.xls’!total,8,FALSE)=0,””,VLOOKUP(C1,’5-392108-Churchtosend.xls’!total,8,FALSE))

This will put in a null string rather than the 0 if it is blank.

Steve

Reply | Quote

The result of a lookup “of a blank cell” always results in a zero. It is just like using something like:
=A1 will give a 0 if A1 is blank.

There are a couple workarounds: instead of using a blank cell in the ‘ChurchFunds” sheet, add a space to those cells, then a space will be “looked up” and it will display the space in your output.

Another means is to change the formula:
In your “amount column” use a variant of (change the lookup column as desired):

=IF(C3="","",VLOOKUP(C1,'5-392108-Churchtosend.xls'!total,7,FALSE))

and in your check num:
=IF(VLOOKUP(C1,’5-392108-Churchtosend.xls’!total,8,FALSE)=0,””,VLOOKUP(C1,’5-392108-Churchtosend.xls’!total,8,FALSE))

This will put in a null string rather than the 0 if it is blank.

Steve

Reply | Quote

Thanks everyone for all your help. I

Reply | Quote