Complex Scorecarding

Options

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

  • Jaykel Torres
    Jaykel Torres Employee
    Answer ✓
    Options

    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

  • Jaykel Torres
    Jaykel Torres Employee
    Answer ✓
    Options

    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
    Options

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