Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quarter Reports (2000)

    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

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Quarter Reports (2000)

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Quarter Reports (2000)

    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

  4. #4
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quarter Reports (2000)

    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Quarter Reports (2000)

    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 <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>,

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quarter Reports (2000)

    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:

    <pre>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
    </pre>


    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.

  7. #7
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quarter Reports (2000)

    Thanks everyone for all your help. I

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Quarter Reports (2000)

    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):
    <pre>=IF(C3="","",VLOOKUP(C1,'5-392108-Churchtosend.xls'!total,7,FALSE))</pre>


    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •