Template for Scoring Grant Proposals?

Nicole Endsley

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?

Genevieve P.

Hi @Nicole Endsley

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

Genevieve P.

Hi @Nicole Endsley

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

Genevieve P.

Hi @Nicole Endsley

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

Genevieve P.

Hi @Nicole Endsley

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

Nicole Endsley

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?

Genevieve P.

Hi @Nicole Endsley

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

Nicole Endsley

@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.

Genevieve P.

Hi @Nicole Endsley

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

Nicole Endsley

@Genevieve P. Thank you so much! This is making my project much more manageable! You're the best!

Genevieve P.

Oh wonderful! I'm so glad. Let me know if there's anything else I can help with as you set up your sheets.