How to work with multiple criteria evaluation?

Christine Rosenberg

I want to create an evaluation for 5 people on 7 criteria in Smartsheet.

If I use form or Request Update workflow, then I can only create one input per item/line. However, I want 5 evaluations for each item/line. Do I create indended line and sum? Do I create seperate sheets for each person evaluating and then sum the evalution in an additional sheet?

Is there a functionality in Dynamic View, where I can do this?

This is an example, where I want the line with reference number 016 to be evaluated for Scientific novelty/Solidity/unmet needs/Market approach. In this dynamic view, I can only have one person check the boxes. How is it possible to have a matrix, where I can have 5 people check a box for ie scientific novelty for reference 016?

Genevieve P.

Hi Christine,

You are correct - this would require you to know who the reviewers are. I'm not quite sure I understand what it is you're reviewing - are you having these 5 people review the Applicant? Or the Title? And are they only doing this by checking a box?

If you don't know who the reviewer is, you could set up a generic form instead that's built from this sheet (this would be instead of the child-row solution above). Users don't need to be shared to a sheet to fill out a form, so there is no need for Dynamic View.

The form could have a restricted drop-down list for what it is that they are reviewing to choose from. (Note: If you don't want them to choose, you would need to set up individual forms for each option: one for Dorthes dukke, one for Postbud, etc. In this case, include the field in the form as a "hidden filed" and set the default value to be what the form should be used for. )

If you make the form restricted to those with a Smartsheet login, you can input a Created by System column to record the user who filled out the form automatically. In addition, you can set up an INDEX(MATCH formula in your sheet to return the reference number or applicant name, depending on what these refer to.

Then once all the reviews have come in, you can Sort the sheet to have the rows collected together, in order. You can either use a second sheet with cross-sheet references to pull different calculations or use the Sheet Summary field, depending on what information you're looking to gather. Another way to sort/filter the data collected would be to create a Report based on specific criteria.

In regards to comments, you could have this be a new column on your sheet, and add this to the form as well. For collecting all comments that have to do with one type of review, you could potentially use a JOIN(COLLECT formula in a different sheet to gather this information into one cell, but this might look quite large. I would suggest that creating a Report or Sorting the rows so the comments show up in order might be the simplest way to go.

If you want it to be obvious which comment pertains to which reference number, you could set up conditional formatting rules to change the colour of the comment cell depending on the reference number.

I know this is a lot of information, so please let me know if I can go into further detail for any of these elements. If this won't work for what you're trying to do, it would be helpful to hear a bit more about the overall picture and end goal of what this sheet is used for, along with any more screen captures or examples.

Thanks!

Genevieve

Genevieve P.

Hi Christine,

If I understand you correctly, you're looking to have 5 different users mark the same checkboxes per line-item. Your suggestion of having an indented line, or child rows, would be what I would set up in this scenario:

You'll notice I added in an extra column that has the Evaluator listed in a Contact Column. You could then configure your Dynamic View to only show the rows assigned to the current user:

This would allow each evaluator to go in and check all the boxes needed per person. Your source sheet would then populate with all of the different answers from each row. For example, this would be my view (notice I can't see anyone else's rows, or any of the fields that have formulas):

As a final note, I'm not sure what the Evaluation Count column is - is this just totalling up the number of boxes checked for that row? If so, you could either have the Parent row calculate the overall total from all the child (indented) rows like so: =SUM(CHILDREN()) or you could calculate the average (like in my screen capture above) like so: =AVG(CHILDREN()). These formulas will update automatically as the Dynamic view is filled in.

Let me know if this works for what you are looking to do, or if I have misunderstood your question!

Cheers,

Genevieve

Christine Rosenberg

Hi @Genevieve P

Thank you for a very elaborate answer 😊 I like the idea with intended rows (children). However, doesn't this solution require that I have predefined the reviewers by name?

Is there a solution, where the reviewers can change from time to time, and have the form populate with children, instead of being predefined?

another topic when working with children - each reviewer will make comments... how do I gather them from 5 different rows?

Genevieve P.

Hi Christine,

You are correct - this would require you to know who the reviewers are. I'm not quite sure I understand what it is you're reviewing - are you having these 5 people review the Applicant? Or the Title? And are they only doing this by checking a box?

If you don't know who the reviewer is, you could set up a generic form instead that's built from this sheet (this would be instead of the child-row solution above). Users don't need to be shared to a sheet to fill out a form, so there is no need for Dynamic View.

The form could have a restricted drop-down list for what it is that they are reviewing to choose from. (Note: If you don't want them to choose, you would need to set up individual forms for each option: one for Dorthes dukke, one for Postbud, etc. In this case, include the field in the form as a "hidden filed" and set the default value to be what the form should be used for. )

If you make the form restricted to those with a Smartsheet login, you can input a Created by System column to record the user who filled out the form automatically. In addition, you can set up an INDEX(MATCH formula in your sheet to return the reference number or applicant name, depending on what these refer to.

Then once all the reviews have come in, you can Sort the sheet to have the rows collected together, in order. You can either use a second sheet with cross-sheet references to pull different calculations or use the Sheet Summary field, depending on what information you're looking to gather. Another way to sort/filter the data collected would be to create a Report based on specific criteria.

In regards to comments, you could have this be a new column on your sheet, and add this to the form as well. For collecting all comments that have to do with one type of review, you could potentially use a JOIN(COLLECT formula in a different sheet to gather this information into one cell, but this might look quite large. I would suggest that creating a Report or Sorting the rows so the comments show up in order might be the simplest way to go.

If you want it to be obvious which comment pertains to which reference number, you could set up conditional formatting rules to change the colour of the comment cell depending on the reference number.

I know this is a lot of information, so please let me know if I can go into further detail for any of these elements. If this won't work for what you're trying to do, it would be helpful to hear a bit more about the overall picture and end goal of what this sheet is used for, along with any more screen captures or examples.

Thanks!

Genevieve

Christine Rosenberg

Hi @Genevieve P - thank you so much for the answer :-)

Genevieve P.

No problem! I'm glad that the forms solution will work for you. 😊