Template for Scoring Grant Proposals?
Is there a template set that could be used/modified to suit the following need:
A team of 4 is scoring grant applications. We're using a form to gather scores for 100 applications (estimated), 4 scorers, 15 scoring categories/domains. I'm trying to set up a metadata sheet to collect everyone's scores. I need a single line for each grantee/applicant that collects all of the scores from each reviewer in all 15 scoring categories as well as totals/averages the scores. I need the single line so I can use documentbuilder to export a "letter" to each applicant showing their scores. I tried making my own and using parent/children but couldn't get it work like I wanted. I think a template would be a great starting pointif one exists?
Best Answers

The way I would do this is to have two sheets:
 one with the form that receives the 4 score submissions per applicant
 one that creates the totals and exports the row to Document Builder
You would need to have a column filled out in your second sheet with a unique identifier (e.g. the applicant name or ID), then use either SUMIF or AVERAGIF formulas to create your calculations by looking into your first sheet using crosssheet references. For example:
=SUMIF({Name Column}, Name@row, {Column to Sum})
See: Create cross sheet references to work with data in another sheet
I would personally use a symbol in this second sheet to indicate when all 4 submissions have been completed for each person/row:
=IF(COUNTIF({Name Column}, Name@row) = 1, "Quarter", IF(COUNTIF({Name Column}, Name@row) = 2, "Half", IF(COUNTIF({Name Column}, Name@row) = 3, "Three Quarters", IF(COUNTIF({Name Column}, Name@row) = 4, "Full", "Empty"))))
Although you couldn't use this as a trigger to generate the document, you could put this as a condition in a workflow so it only runs if the Scores Completed is at "Full".
Let me know if this makes sense and will work for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Importing the scores is where you would need to use crosssheet formulas to look into your sheet with 4 rows and consolidate that information into one cell for one row.
What formulas you use is dependent on how you are doing your scores. For example, you could SUM together the 4 values for one total value:
=SUMIF({Name Column}, Name@row, {Column to Sum})
Or you could average the values with AVERAGEIF:
=AVERAGEIF({Name Column}, Name@row, {Column to Avg})
See: Create cross sheet references to work with data in another sheet
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
That's great to hear! 🙂
To bring over cell data, my preferred formula is the combination of INDEX(MATCH.
The structure works like this:
=INDEX({Column to bring back Sheet 2}, MATCH([Matching Value]@row, {Column with matching value Sheet 2}, 0))
So in your instance:
=INDEX({Applicant Name Column}, MATCH([ID Number]@row, {ID Number Column}, 0))
Here's a help article with more details: Formula combinations for cross sheet references
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

The way I would do this is to have two sheets:
 one with the form that receives the 4 score submissions per applicant
 one that creates the totals and exports the row to Document Builder
You would need to have a column filled out in your second sheet with a unique identifier (e.g. the applicant name or ID), then use either SUMIF or AVERAGIF formulas to create your calculations by looking into your first sheet using crosssheet references. For example:
=SUMIF({Name Column}, Name@row, {Column to Sum})
See: Create cross sheet references to work with data in another sheet
I would personally use a symbol in this second sheet to indicate when all 4 submissions have been completed for each person/row:
=IF(COUNTIF({Name Column}, Name@row) = 1, "Quarter", IF(COUNTIF({Name Column}, Name@row) = 2, "Half", IF(COUNTIF({Name Column}, Name@row) = 3, "Three Quarters", IF(COUNTIF({Name Column}, Name@row) = 4, "Full", "Empty"))))
Although you couldn't use this as a trigger to generate the document, you could put this as a condition in a workflow so it only runs if the Scores Completed is at "Full".
Let me know if this makes sense and will work for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thanks for the response! I can get on board with two sheetsbut how would I automate the import of scores from 4 different rows (4 different scorers submitting to the form) to one row in the docubuilder sheet?

Importing the scores is where you would need to use crosssheet formulas to look into your sheet with 4 rows and consolidate that information into one cell for one row.
What formulas you use is dependent on how you are doing your scores. For example, you could SUM together the 4 values for one total value:
=SUMIF({Name Column}, Name@row, {Column to Sum})
Or you could average the values with AVERAGEIF:
=AVERAGEIF({Name Column}, Name@row, {Column to Avg})
See: Create cross sheet references to work with data in another sheet
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. This is working! Thank you! How can I write a formula for this:
In Sheet B, I'd like to copy over the name of the applicant from Sheet A, but only if the ID Number matches. I have a column in both sheets that says ID Number.

That's great to hear! 🙂
To bring over cell data, my preferred formula is the combination of INDEX(MATCH.
The structure works like this:
=INDEX({Column to bring back Sheet 2}, MATCH([Matching Value]@row, {Column with matching value Sheet 2}, 0))
So in your instance:
=INDEX({Applicant Name Column}, MATCH([ID Number]@row, {ID Number Column}, 0))
Here's a help article with more details: Formula combinations for cross sheet references
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. Thank you so much! This is making my project much more manageable! You're the best!

Oh wonderful! I'm so glad. Let me know if there's anything else I can help with as you set up your sheets.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives