Okay, I have played with this for over a month now so I am going to ask the question from square one and tell you what I've already done to see if anyone can help me figure out how to do this, because I am certain at this point I am making it harder than it needs to be 😂
I will redact some of these screenshots for privacy
- I used the "Inspection Tracking" template set, which comes with a form sheet, a metrics sheet, and a dashboard.
- I made the initial form, which consists of 8 sections, with 27 questions split between the sections See 1st Photo
- Columns are shown as PM1, PM2, etc.; the initial corresponds with the information being gathered in that section
- This form is sent to floors of a hospital to answer questions about compliance in each section, therefore some "violations" or wrong answers are no and some are yes
- There is not a "date" column on this form, but a dropdown for them to choose what month it was completed (January 2023, February 2023, March 2023....)
- On the metrics page, I have created formulas to count the total violations for each section, also to count the total violations for each section by month, and to count the total violations all together See 2nd Photo
- Here's where I need help:
- I want to now take these violations and separate them out by the area that submitted the information (on the form photo you will see areas such as B8 - General Medical, C9 - Telemetry, A2 - CVICU, etc) to show the total violations for each area by month.
- I want to be able to take the violations by unit by month and compare them to the total submissions by unit by month to show a percentage of compliance
I have tried every way that I can think of, watched videos, read community posts and I can not get a formula to make this work. If it is not doable, that is fine, but I would rather someone who is far more experienced in Smartsheet make that call than me since I am still very new and learning. I can't imagine it isn't possible, but I also know I could be wrong.
I hope this makes sense, I can share more if needed.
