Sheet to Dashboard Help *LONG post!*

tmichelle068

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

1. I used the "Inspection Tracking" template set, which comes with a form sheet, a metrics sheet, and a dashboard.
2. I made the initial form, which consists of 8 sections, with 27 questions split between the sections See 1st Photo
   1. Columns are shown as PM1, PM2, etc.; the initial corresponds with the information being gathered in that section
   2. 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
   3. 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....)
3. 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
   1. Here's where I need help:
      1. 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.
      2. 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.

Lucas Rayala

Hi @tmichelle068, your explanation is a bit complicated and I'm not sure exactly what you're looking for. I'll provide a formula based off what I think you're saying, feel free to correct my assumptions. I'll assumer there is a date column I can not see called "Date" and a "Yes" value is a violation.

An example of what I believe you are trying to do is, for column PM1, provide a count of all "Yes" values where the Area Surveyed column is "A9 Pulmonary" and the Date column is "January 2023".

That formula would be:

=COUNTIFS(PM1:PM1, "Yes", [Area Surveyed]:[Area Surveyed], "A9 Pulmonary", Date:Date, "January 2023")

To count all submissions (both "Yes" and "No") for this group, the formula would be:

=COUNTIFS(PM1:PM1, OR(@cell="Yes", @cell="No"), [Area Surveyed]:[Area Surveyed], "A9 Pulmonary", Date:Date, "January 2023")

You can remove "PM1:PM1, OR(@cell="Yes", @cell="No")" completely if you aren't worried about blanks or anything like that.

To get the percentage, just divide these two formulas:

=(COUNTIFS(PM1:PM1, "Yes", [Area Surveyed]:[Area Surveyed], "A9 Pulmonary", Date:Date, "January 2023")) / (COUNTIFS(PM1:PM1, OR(@cell="Yes", @cell="No"), [Area Surveyed]:[Area Surveyed], "A9 Pulmonary", Date:Date, "January 2023"))

That seems a bit simplistic, so feel free to provide feedback.