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 :-)
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 68K Get Help
- 474 Global Discussions
- 209 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 85 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives