Thoughts/Suggestions Welcome

Marlana K. · 11/30/23

I am in the process of updating a very old (circa 2016) Application and Request for Charitable Donations form. Currently all of the process after submission are manual. Most of the manual process will now be automated using Smartsheet.

We have 700+ Sites that may request donations. We have one individual that reviews the request each month and manually calculates the amount requested to date.

Here is where I am struggling. We need to track each site's request

1.) Requesting Organization, Site Number, Amount Requested -this is doable through a report.

2.) Next we need to calculate Last Year's $'s and This Years $'s this is where it gets tricky and where I am looking for suggestions.

The Site ID's are all in ONE Cell in the current form along with notes such as divide equally between site IDs or Site ID x receives $x and Site ID y receives $y. I have created additional columns and separated the site id's into those columns and a notes column. In the new form I have created a site ID dropdown and Additional Site ID (1) - (5) fields and a notes so that they cannot put all of this in one cell. We also move completed request to Archive Sheet (Intake to Archive process).

Now I need to create a magical formula that looks at all site id columns and notes and calculates the $ amounts for this year and last year referencing the current sheet and the archive sheet.

Tired of racking my brain to make it work.

Only thought I have is remove the notes section and add an individual Additional Site ID (1) $Amount for each additional site id box 1-5. Then create an additional "reference sheet that I can use indexing in.

I am sure this is where additional Add-in such as Data Shuttle or Pivot would be a huge help but we do not have them.

IDEAS WELCOME!

Thanks in Advance

Marlana

jmyzk_cloudsmart_jp · 12/03/23

Hi @Marlana K.

Here is a demo solution for your situation.

I adopted your idea of removing the notes section and adding an individual Additional Site ID (1) $Amount for each additional site ID box 1-5. ([Split Site ID# (0)]-[Split Site ID# (4)] columns in my example. In the current sheet, if you input each % of Split0 to Split4, formulas calculate each site's $ amount.)

The "calculate sites amounts: this year & last year" sheet below uses the Site ID# value to sum the $ amount for each Split Site ID#( )' value for this year and last year.

The formula for this year is:

[Split Site ID# (0)]=SUM(COLLECT({current sheet Range Split0}, {current sheet Range Site0}, VALUE([Site ID#]@row) = VALUE(@cell)))

Please look at the published demo dashboard and the links to other sheets for details.

https://app.smartsheet.com/b/publish?EQBCT=968b396554e34317ad413a8ab64216fb

Links for sheets

current sheet

https://app.smartsheet.com/b/publish?EQBCT=43f5068d28fc4ae8a1760d682165e359

https://app.smartsheet.com/b/publish?EQBCT=89cdddad85e84d29a0eeed11a33bfa9e

calculate sites amounts: this year & last year

https://app.smartsheet.com/b/publish?EQBCT=71978085c7574f6f9faf3cb4e99ddee0

https://app.smartsheet.com/b/publish?EQBCT=5749de10cb884438b1f2799b983cef21

Copy of this solution

For a copy of this solution, please request from this form; (choose "sites amounts: this year & last year")

https://app.smartsheet.com/b/form/c06619d504f9418284b4125eec205a54

Detailed description:

The current sheet and archive sheet have columns, Split Site ID# (0) to Split Site ID# (4), with the $ amount for the site in the Site Number column and Additional Side ID#( ) columns. (The % of Split0 to Split4 calculates the $ amount values)

The "calculate sites amounts: this year & last year" sheet below uses the Site ID# value to sum the $ amount for each Split Site ID#( )' value for this year and last year.

The formula for this year is:

[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 Split1}, {current sheet Range Site1}, VALUE([Site ID#]@row) = VALUE(@cell)))

The formula for last year is:

=SUM(COLLECT({archive sheet Range Split0}, {archive sheet Range Site0}, VALUE([Site ID#]@row) = VALUE(@cell)))

Sheet References:

{current sheet Range Split0} is current sheet's [Split Site ID# (0)] range.

{current sheet Range Site0} is current sheet's [Site ID#] column range.

{current sheet Range Site1} is current sheet's [Additional Site ID# (1)] column range.

The ranges for the archive sheet have the same pattern as the ranges for the current sheet.

Marlana K. · 12/04/23

Hi @jmyzk_cloudsmart_jp ,

THANK YOU! This actually blows my mind, now need to dig in and make sense of it.

Marlana

jmyzk_cloudsmart_jp · 12/04/23

Hi @Marlana K.

Happy to help!😁 Ask me any questions if you have any.

Marlana K. · 12/05/23

@jmyzk_cloudsmart_jp

Quick question, I see that I may request a copy of this solution, is this something we will be billed for through Smartsheet?

Thanks,

M

jmyzk_cloudsmart_jp · 12/05/23

@Marlana K.

This service is my pro bono activity.😁

Marlana K. · 12/05/23

Thank you! Wasn't sure how that worked.

I honestly was not expecting to receive more than a few suggested formulas. I have updated the form/Intake Sheet and have been able to create and incorporate the calculate sites this year and last year sheet into my solution.

Thank you for providing a learning opportunity!

"M"

jmyzk_cloudsmart_jp · 12/05/23

@Marlana K.

I used to distribute New Year's card donations to non-profit organizations and was curious about your situation. Please don't hesitate to contact me if you need any assistance with your work. I'm happy to help.😀

Marlana K. · 12/12/23

@jmyzk_cloudsmart_jp I have my Demo with our Director Client Services and Acct Mgt. fingers crossed!!!!

Thoughts/Suggestions Welcome

Answers

Help Article Resources

Categories