first
Marlana K. needed to update a very old (circa 2016) charitable donation application and request form. However, the post-submission process was entirely manual, requiring us to manually review requests from over 700 sites every month and calculate donations for last year and this year.
jmyzk_cloudsmart_jp proposed the following demo solution.
Question summary
Marlana wanted an efficient way to track requests for each site. Specifically, we wanted to automate the following items:
- Requesting organization, site number, and amount requested for each site
- Donation amount from each site last year and this year
Answer summary
jmyzk_cloudsmart_jp suggested the following solution.
- Remove the Notes section and add separate Additional Site ID (1) $Amount fields for each Additional Site ID box 5-1.
- Create a "Calculate site amount: This year and last year" sheet that totals the donation amount for each site ID for this year and last year.
- Use a formula to automatically calculate the donation amount for each site.
Summary
- Automating old donation forms can significantly reduce the burden of manual data entry and calculations.
- The solution proposed by jmyzk_cloudsmart_jp is realized by leveraging Smartsheet's formula and reference features.
- This solution may be useful for nonprofit organizations and other charities.
Keyword
- Donation application form
- request form
- 自动化
- Smartsheet
- Formula
- reference
Supplemental
- jmyzk_cloudsmart_jp provides a form to request a copy of this solution.
- jmyzk_cloudsmart_jp provides consulting services for NPOs.
note: The above solution is based on the demo provided by jmyzk_cloudsmart_jp. Actual solutions may vary depending on your individual needs.
Translation of correspondence
Marlana K. ✭✭✭✭✭
I am updating a very old (circa 2016) charitable donation application and request form. Currently, all post-submission processes are manual. Most manual processes will now be automated using Smartsheet.
There are over 700 sites asking for donations. We have one person who reviews requests every month and manually calculates the amount requested so far.
Here's where I'm struggling. I need to track requests for each site
1.) Requesting organization, site number, and amount requested – this can be done through reporting.
2.) Next, you need to calculate last year's $ and this year's $. This is where it gets tricky and where I'm looking for suggestions.
In its current format, the site IDs are all in one cell, with notes to split evenly between the site IDs, or site ID x receives $x, site ID y receives $y, etc. . I created additional columns and separated the site ID into those columns and a notes column. In the new form, we created a site ID dropdown, additional site ID (1)-(1) fields, and a note so we can't put them all in one cell. It also moves completed requests to the archive sheet (ingest-to-archive process).
Now you need to go through all the site ID columns and notes and create a magic formula that looks at the current sheet and the archived sheets to calculate this year's and last year's amounts.
I'm tired of racking my brain to make it work.
All I'm thinking of doing is removing the notes section and adding a separate Additional Site ID (1) $Amount for each Additional Site ID box 5-1. Next, create additional "reference sheets that can be used for indexing."
I'm sure additional add-ins like Data Shuttle or Pivot would be a big help in this case, but we don't have them.
Ideas welcome! Thanks in advance
answer
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
Hello@Marlana K.
This is a demo solution for your situation.
The idea was to remove the notes section and add a separate additional site ID (1) $Amount for each additional site ID box 5-1. (In this example, the Split Site ID# (0) – Split Site ID# (4) columns. In the current sheet, as you enter each % from Split0 to Split4, the formula calculates the $ amount for each site. )
The Calculate Site Amounts: This Year and Last Year sheet below uses the site ID# values to sum the amounts for each split site ID#( ) for this year and last year.
This year's ceremony is as follows:
[Split site ID# (0)]=SUM(COLLECT({current sheet range Split0}, {current sheet range Site0}, VALUE([site ID#]@row) = VALUE(@cell)))
For more information, see the public demo dashboard and links to other sheets.
https://app.smartsheet.com/b/publish?EQBCT=968b396554e34317ad413a8ab64216fb
Link to sheet
current sheet
https://app.smartsheet.com/b/publish?EQBCT=43f5068d28fc4ae8a1760d682165e359
https://app.smartsheet.com/b/publish?EQBCT=89cdddad85e84d29a0eeed11a33bfa9e
Calculate the amount on the site: this year and last year
https://app.smartsheet.com/b/publish?EQBCT=71978085c7574f6f9faf3cb4e99ddee0
https://app.smartsheet.com/b/publish?EQBCT=5749de10cb884438b1f2799b983cef21
Copy of this solution
If you would like a copy of this solution, please request it using this form. (Select “sites amounts: this year & last year”)
https://app.smartsheet.com/b/form/c06619d504f9418284b4125eec205a54
Detailed explanation:
The current and archive sheets have columns from Split Site ID# (0) to Split Site ID# (4), with site amounts displayed in the Site Number and Additional Side ID#( ) columns. (% of Split0 to Split4 calculates the $ amount value)
The Calculate Site Amounts: This Year and Last Year sheet below uses the site ID# values to sum the amounts for each split site ID#( ) for this year and last year.
This year's ceremony is as follows:
[Split site ID# (0)]=SUM(COLLECT({current sheet range Split0}, {current sheet range Site0}, VALUE([site ID#]@row) = VALUE(@cell)))
[Split site ID# (1)]==SUM(COLLECT({Current sheet range split 1}, {Current sheet range site 1}, VALUE([Site ID#]@row) = VALUE(@cell)) )
Last year's ceremony was as follows:
=SUM(COLLECT({Archive Sheet Range Split0}, {Archive Sheet Range Site0}, VALUE([Site ID#]@row) = VALUE(@cell)))
Sheet reference:
{Current Sheet Range Split0} is the [Split Site ID# (0)] range of the current sheet.
{Current sheet range Site0} is the [Site ID#] column range of the current sheet.
{Current Sheet Range Site1} is the Additional Site ID# (1) column range of the current sheet.
The archive sheet range has the same pattern as the current sheet range.
Marlana K. ✭✭✭✭✭
Hello@jmyzk_cloudsmart_jp ,
thank you! This really surprised me. Now we need to dig into it and understand it.
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
Hello@Marlana K.
We will be happy to help!😁 If you have any questions, please feel free to contact us.
Marlana K. ✭✭✭✭✭
@jmyzk_cloudsmart_jp
Just a quick question: I see that I can request a copy of this solution, but is this something I can request through Smartsheet?
thank you.
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
@Marlana K.
This service is my pro bono activity 😁
Marlana K. ✭✭✭✭✭
thank you! I didn't understand how that would work.
Honestly, I wasn't expecting to receive anything more than a few suggestions. I was able to update the forms/intake sheets and create sheets for this year's and last year's calculation sites and incorporate them into the solution.
Thank you for providing this learning opportunity!
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
@Marlana K.
I used to distribute New Year's card donations to NPOs, so I was interested in your situation. Please feel free to contact us if you need any assistance with your work. We will be happy to assist you. 😀
Marlana K. ✭✭✭✭✭
@jmyzk_cloudsmart_jp There is a demo using Director Client Services and Acct Mgt. I wish you success! ! ! !