Complex Scorecarding

Jermain Johnson

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.

Jaykel Torres

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

Jaykel Torres

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

Joerg Schmidt

could you please show where you put the reference (f.e. "sheet extra") when the values are written in a separate sheet. Thank you :-)