We are in the process of creating a Year End Tax report for our donors. I had posted a request to the shared reports forum to see if any one had such a report available. However, it does not seem to be the case? Once I have ours , I am happy to share it if there is any interest.
While putting together the specifications I do have some questions though, that I was hoping to get some feedback on.
Do you ever get requests from donors for calendar year-end tax receipts, and how do you fulfill them?
When receipting a pledge with a non-dedcutible portion that spans multiple calendar years, do you account for the whole value of goods and services in the first year or do you pro-rate that?
thanks for your feedback!!
Monika
Do you know how to access the VBA developer area in your workbook? (It's simple once you've done it a couple of times: https://www.vertex42.com/blog/excel-formulas/custom-user-defined-functions.html)
Once you're there, insert the code below as a module. This creates a User Defined Function (basically, a custom function) that you can use in your spreadsheet.
Function LookUpConcat(ByVal SearchString As String, _ SearchRange As Range, _ ReturnRange As Range, _ Optional Delimiter As String = " ", _ Optional MatchWhole As Boolean = True, _ Optional UniqueOnly As Boolean = False, _ Optional MatchCase As Boolean = False) Dim X As Long, CellVal As String, ReturnVal As String, Result As String If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _ (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then LookUpConcat = CVErr(xlErrRef) Else If Not MatchCase Then SearchString = UCase(SearchString) For X = 1 To SearchRange.Count If MatchCase Then CellVal = SearchRange(X).Value Else CellVal = UCase(SearchRange(X).Value) End If ReturnVal = ReturnRange(X).Value If MatchWhole And CellVal = SearchString Then If UniqueOnly And InStr(Result & Delimiter, Delimiter & _ ReturnVal & Delimiter) > 0 Then GoTo Continue Result = Result & Delimiter & ReturnVal ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then If UniqueOnly And InStr(Result & Delimiter, Delimiter & _ ReturnVal & Delimiter) > 0 Then GoTo Continue Result = Result & Delimiter & ReturnVal End IfContinue: Next LookUpConcat = Mid(Result, Len(Delimiter) + 1) End If End Function
The formula in our spreadsheet looks like this:
=LookUpConcat($A3,$A$2:$A$470,$P$2:$P$470,CHAR(10))
$A3 is where the customer_id value is$A$2:$A$470 is the column with all the customer_ids$P$2:$P$470 is the column with the texts we want to concatenate IF the customer_id on that row matches $A3CHAR(10) is the separator between the different texts (10 is the ASCII code for line break)
This is a bit convoluted but in absence of a database administrator it gets the job done!
The result is a bunch of cells containing something like this, which we merge into a Word doc:
$100.00 received on 06/19/2017 designated to Special Gifts$85.00 received on 07/27/2017 designated to Membership Gifts