Solved

Custom Excel Report - church contribution statements

Posted on 2015-01-27
17
871 Views
Last Modified: 2015-02-03
I have an Excel spreadsheet with a church's donations setup for the year.  The spreadsheet is setup with each donor's name address on the row along with their giving totals for each month of the year, and total for the entire year.

I'm wondering if its possible save me a ton of cut and paste work to create a custom report that will auto-populate a report for the donor contribution record.

Here are my columns
Donor Name | Address | Jan | Feb | Mar | Apr | May | June | Jul | Aug | Sept | Oct | Nov | Dec | Year Total

I'd like it to lay out

<header> (I can do this)

<donor name>
<donor Address> (this may require an intermediate step to separate address into columns (address, city,state,zip))

Giving Records

<jan> amount
<feb> amount
<mar> amount
<apr> amount
<may> amount
<june> amount
<july> amount
<aug> amount
<sept> amount
<oct> amount
<nov> amount
<dec> amount
============
<year total>

It would be awesome if I could setup the connections on the report "template" and then just create something to make it run and produce the reports.

Any Suggestions or direction is much appreciated.  I'm not even sure what to call what I'm doing to look up in Google.

Thanks in advance.
0
Comment
Question by:axessJosh
17 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40574440
Excel can do that for you using a function called Transpose. Select the existing data, including column headings, select Copy, click on an empty cell where the data can be pasted, then select Paste Special. In the Paste Special dialog, check the Transpose checkbox, then click OK. Your pasted text will now be transposed so that columns become rows. Once that is done, add a Sum() function to get your totals.
0
 
LVL 7

Expert Comment

by:slubek
ID: 40574762
Use pivot table (see attachment).
Donations.xlsx
0
 
LVL 23

Expert Comment

by:DanCh99
ID: 40574914
It looks like you want Excel to actually write the letters to each of your doners.

You *could* do this, but I'd say it was more of a natural candidate for a mail merge .

It can be a bit fiddly to do this, but not horrific.  If you let us know which version of Office you have, I'm sure we can advise more precisely.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40575884
Thanks DanCh99.

i have pushed forward with mail merge and it's going to do what I want.

there is one catch.

There will always be an <<address block>> but not every month will have data included.

I'd like it to layout the results in a table format but can't figure out how to have it skip if no data exists.  i see that functionality, but not how to use it.

For Instance, I'd like it to layout the results in columns and rows for month | total, but only create the row necessary for the cells with data present.

Any advice?
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576171
In a mail merge you can select "Rules".  Put the cursor next to the January field, then click "Rules". Click "Skip Record If..." Select the field and under Comparison, select "is blank".  Do this next to each month.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576175
Thanks Katie, but how would I create a table for the months that do exist... is it possible to select an entire table row and apply the Rule?
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576182
Sorry, do that rule in Word.  Set up your template, then apply the rule next to each field there.  Leave Excel doc as is.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576188
yes, that is where I am setting it up.

I'd like to create a Table in Word so the data lines up and looks nice and justified.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576200
Oh, I see.  Not sure if you could make it skip the way you want if it is in a table format.  If the months are designated to rows in a table, you could have the data next to them be blank, but I'm not sure you could have it skip it entirely and have the months "move up" so to speak, so as to avoid blank spaces.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576215
gotcha, is there another layout option that I am not considering that would acheive what I'm going for?  Perhaps a horizontal rule, etc.?
0
 
LVL 7

Accepted Solution

by:
Katie Pierce earned 500 total points
ID: 40576233
Here, I used slubek's sample Excel doc and added columns that name the month IF there was donation data for that month (I only did Jan-Mar, for this example).  Then I created a quick letter where I used the field I created, plus the "blank" rule, then hit Tab, so that the next field (the donation amount) lines up on where the cursor tabbed, and applied the same "blank" rule.

The donation data should line up vertically because that field is all lined up on the Tab, not by spaces, so it shouldn't be affected by the length of the Month Name (if it is, just hit Tab again on all of them).

Also, the entire line should blank out if there was no donation that month--month name and amount.
Letter.docx
Donations.xlsx
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576252
this is just about what I need.  The last piece is that I have the month titles as column headers.  Is there a way to pull that info as the description with the amount?  I also noticed that it's not pulling the data as Currency eventhough that is how its defined in Excel.
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576265
Regarding the months, that's why I created columns that would name the month if there was relevant data, but be blank if there were not.  I'm not sure you can combine them.

Regarding the Currency, you can right click on the amount field in Word and click "Edit Field", then check the box on the far right that says, "Text to be inserted before:" then enter the $.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576301
yea, I tried adding a column with months but that doesn't work with my data layout.

the excel is setup

giver 1 | jan | Feb | Mar | etc.
giver 2 | Jan | Feb | mar | etc.

I'm trying some work-arounds with the text before option under Edit Fields, but can't put a tab there...
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576365
What if, using the "edit field" option, instead of just "$" before the amount field you did "January $", "February $", etc before each one?
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576431
I did that, but it goes back to a spacing layout issue... Boo
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576440
Well, you could figure out how much space you need after the longest month (probably September), then increase the spaces by the correct number of characters for each month.

So, if you had:

September  $                                (2 spaces before the $)
October       $                                (7 spaces before the $)

So as I typed it I was proven wrong--October needed 7 spaces, not the anticipated 5.  But still, you could get them close?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now