Help? *LONG POST*

tmichelle068

I'm going to do my best to explain and show a lot of this, thanks in advance for attempting to understand this all 

😂



I used the "Inspection Tracking" template set to create a form, a metrics sheet, and a dashboard. The form works great, not the issue. On the metrics sheet, I am having trouble determining the correct formula to use to gather the information I need. I'll break this part down:

• My main form is split into 9 sections, with each section having several questions that are counted toward a "violation" depending on the answer to the question.
• On the metrics section, I have this broken down by question and am using a =countifs formula to find the violations for each question by month. See the Columns photo attached.
  • You can see not every question is counted, and I have the total for each section for that month calculated below the questions.
• I then have this information funneled into a Violations by Month chart See the Violations by Month photo attached
• Here's what I need to be able to do that I cannot figure out:
  • Violations by unit, by month, for each category
  • Percentage compliant of answers based on how many submissions were made for each unit
• I have another chart showing the total submissions of each unit by month already made as well

I hope this makes a little sense haha, but PLEASE ask me any questions you have. I have been working on this for almost a month now, taking breaks and trying different ways, and I cannot figure this out.

Jeff Reisman

@tmichelle068 I don't see where "Unit" factors in here. There's nothing on either sheet that indicates data for "Unit", so how and where do you count violations per unit? Or how many questions a unit answered?

tmichelle068

Sorry about that! Unit would be the area submitting the form. There are 26 units total, and it is a mandatory section on the form. I assumed I could pull that part from another sheet using a formula.

Jeff Reisman

@tmichelle068 Ok, let's get the semantics straight first.

You've got a form that is filled out by a unit, which creates a row of data on a main sheet, right? And you're not showing us that main sheet here? And there's one main sheet collecting all the form answers for each unit?

If so, does that main sheet have a column indicating the Unit, the date or month the data on that row pertains to, and some formulas that calculate if those answers equal up to a violation for each of these 9 categories?

The metrics sheet, with the black boxes and red Totals rows - is this showing us all the violations for each category for each month? Or just the violations for one unit? And what's covered up by the black boxes?

tmichelle068

Yes, the main sheet has columns for the date and the unit, but not a total violations column. Should each section have a hidden helper column to calculate those, then I could use those columns to calculate my data?

The questions I covered with black boxes just for privacy, but they are showing the violation to that question for that month NOT taking the unit into account.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/363501#Comment_363501

I guess - hard to say without seeing the data structure in the main sheet. That really has to be known in order to effectively pull metrics in different ways. Kind of flying blind here.