I'm trying to find a workaround for the fact that Quickbooks does not have a suitable solution for printing donation receipts that comply with Canada's CRA guidelines. We are not actually using Quickbooks yet, but we need a more formal system, and this seems to be the preferred option, with the exception of this issue. I've been asked to decide on a system by Tuesday and I need to know what will actually work. So far I have been using Excel for all our accounting records, and our process for donation receipting is rather manual. I have accepted this till now but the aggravation of having to move to a new system better lead to some kind of automation somehow!
I have never used VBA and am not familiar with the coding that seems to be bandied about much here, so if coding is what's needed please be patient with me. I do want to learn, and love working in Excel, but may or may not have the time in this context.
Anyways, one thought I had was to export a spreadsheet similar to the one I use now with essentially the transaction data below:
Date
Name
Fund (ie Missions, Benevolent, Regular, & maybe up to a couple others)
Amount
(I would need Address fields too, but could use a vlookup to add them later.)
From this I want to create a donation receipt at the end of the year for each donor (about 25 donors), with each donation listed as a separate entry with date and amount. If other than Regular, would also like to indicate the Fund. As we are a church, I would prefer to use a column format, because there are donors that give a donation every week, so transactions can add up (for these I need 3 columns to keep everything on one page). Since the column format leads to a lack of horizontal space, I've sometimes used a '*' to footnote the fund(s). I would need to generate a total for the amount given during the year, and a unique Receipt Number for each receipt, preferably starting with the number of the last receipt of the previous year, as this is how we have been doing it. If I need to use 3 columns for all receipts, that is ok. The tearaway portion of the receipt to submit to CRA is about 20 lines; I prefer it at the end, and one page per receipt. I have been generating receipts manually till now (copy/paste) with one tab per donor, named for that donor.
Possibly a different format than I've indicated might be better, for example if a one tab solution lends itself to creating each donation receipt as a pdf; also it would be faster to print out. I've also heard there is a way to create the receipts using mail merge, Directory option I think, but have not been able to figure out how to do that either. If anyone has info on that would be great, if it's not too much out of the scope of this forum (Word, but still using Excel for the database)
Essentially, my question may just boil down to this:
Is there a formula (set) I could use that would return the next transaction for a donor if it exists, and once there are no more, it could presumably just use an if function to return blanks for the rest? Or is there a more elegant solution that is still straightforward?
Thanks in advance for any help/efforts!
I have never used VBA and am not familiar with the coding that seems to be bandied about much here, so if coding is what's needed please be patient with me. I do want to learn, and love working in Excel, but may or may not have the time in this context.
Anyways, one thought I had was to export a spreadsheet similar to the one I use now with essentially the transaction data below:
Date
Name
Fund (ie Missions, Benevolent, Regular, & maybe up to a couple others)
Amount
(I would need Address fields too, but could use a vlookup to add them later.)
From this I want to create a donation receipt at the end of the year for each donor (about 25 donors), with each donation listed as a separate entry with date and amount. If other than Regular, would also like to indicate the Fund. As we are a church, I would prefer to use a column format, because there are donors that give a donation every week, so transactions can add up (for these I need 3 columns to keep everything on one page). Since the column format leads to a lack of horizontal space, I've sometimes used a '*' to footnote the fund(s). I would need to generate a total for the amount given during the year, and a unique Receipt Number for each receipt, preferably starting with the number of the last receipt of the previous year, as this is how we have been doing it. If I need to use 3 columns for all receipts, that is ok. The tearaway portion of the receipt to submit to CRA is about 20 lines; I prefer it at the end, and one page per receipt. I have been generating receipts manually till now (copy/paste) with one tab per donor, named for that donor.
Possibly a different format than I've indicated might be better, for example if a one tab solution lends itself to creating each donation receipt as a pdf; also it would be faster to print out. I've also heard there is a way to create the receipts using mail merge, Directory option I think, but have not been able to figure out how to do that either. If anyone has info on that would be great, if it's not too much out of the scope of this forum (Word, but still using Excel for the database)
Essentially, my question may just boil down to this:
Is there a formula (set) I could use that would return the next transaction for a donor if it exists, and once there are no more, it could presumably just use an if function to return blanks for the rest? Or is there a more elegant solution that is still straightforward?
Thanks in advance for any help/efforts!
