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 point--if 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 cross-sheet 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 cross-sheet 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 cross-sheet 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 sheets---but 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 cross-sheet 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