Thoughts/Suggestions Welcome


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.


Thanks in Advance



Sr. Business Analysts / Smartsheet Solutions

National Pharmacy Services | Genoa Healthcare


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!