Complex Scorecarding
Has anyone successfully built complex scorecarding in Smartsheet? I would like to be able to gather the data using Smarthsheet and have those entries roll to a data sheet for the analytics. Questions will have categories and subcategories.
Best Answer
-
Hey @Jermain Johnson,
If your Scorecard system is based on taking user input wherein these values would represent numerical data, I recommend creating a Sheet/Grid that can relate the data together. I've created the example below on how this may look like:
Formula: =INDEX([Numerical Value]1:[Numerical Value]3, MATCH([Selected Form Value 1]@row, Text1:Text3), 0) + INDEX([Numerical Value]1:[Numerical Value]3, MATCH([Selected Form Value 2]@row, Text1:Text3), 0) + INDEX([Numerical Value]1:[Numerical Value]3, MATCH([Selected Form Value 3]@row, Text1:Text3), 0)
In this example, I am setting everything up on the same sheet but you can use Cross Sheet References instead for the Formulas. Essentially we are using the INDEX/MATCH Formula to return a corresponding numerical value depending on the Form Value selected. This formula is simply adding each Form Values' Numerical Value together.
I hope this helps!
Jaykel
Answers
-
Hey @Jermain Johnson,
If your Scorecard system is based on taking user input wherein these values would represent numerical data, I recommend creating a Sheet/Grid that can relate the data together. I've created the example below on how this may look like:
Formula: =INDEX([Numerical Value]1:[Numerical Value]3, MATCH([Selected Form Value 1]@row, Text1:Text3), 0) + INDEX([Numerical Value]1:[Numerical Value]3, MATCH([Selected Form Value 2]@row, Text1:Text3), 0) + INDEX([Numerical Value]1:[Numerical Value]3, MATCH([Selected Form Value 3]@row, Text1:Text3), 0)
In this example, I am setting everything up on the same sheet but you can use Cross Sheet References instead for the Formulas. Essentially we are using the INDEX/MATCH Formula to return a corresponding numerical value depending on the Form Value selected. This formula is simply adding each Form Values' Numerical Value together.
I hope this helps!
Jaykel
-
could you please show where you put the reference (f.e. "sheet extra") when the values are written in a separate sheet. Thank you :-)
