collecting rankings on sheet data

vakhanka

Hi folks,

What's the best way for me to set up the following workflow. I want committee members to evaluate candidates for an award and rank their top five in order of preference. I want the reviewers to see all candidates (primary column + attachment), but I don't want the reviewers to see each other's selections.

I tried to do it via the "request update" by sending each committee member an individual ranking field. It provided the privacy I was looking for, but is tedious to click through 20 nominees to a) identify your top five b) rank them in order of preference. It would be easier to see all candidates in a list and then pick out the ones you want. Additionally, committee members may be reviewing nominees not in one pass and the request update does not let one edit their submission.

I tried dynamic view, but there isn't a way to limit columns by row data, only rows by column data. Is there some other solution that I am not envisioning? I have Workapps, Data Shuttle, and Pivot as add-ons.

https://app.smartsheet.com/b/publish?EQBCT=23e23e5750074eb48c3c2bd1d2937e25

Darla Brown

Hi,

If you set up a filter for Current User on the column where the reviewers are listed, then use that filter in your Dynamic View. When the reviewer logs in, they only see what they are tasked with.

Does that work?

vakhanka

@Darla Brown Thank you very much. If I understood this correctly, then sadly this did not solve the issue because the filter only works on rows not columns. I don't see a way to set up in the worksheet a filter called Goofy that only shows column "Rating 1" and another filter called Mickey that only shows column "Rating 2."

Darla Brown

@vakhanka Oh I understand now. In this set up, you would need 2 different DV for that to work.

What about if you put the candidates in separate columns and the reviewers in rows? Then their rankings only show on their row.

vakhanka

@Darla Brown I considered it, but I need each candidate to have an easily accessible attached file that contains their letters of support and credentials. Smartsheet doesn't let you add attachments to a column.

Darla Brown

Oh yes, that is correct. I'm sure there's a way. Hopefully someone will have a better solution for you!

@Paul Newcome @Kyle Chipman @Will Jeffords Any ideas?

MarceHolzhauzen

Heya team!

We've built a similar solution for a customer. @Philip Robbins might have some quick advise here for a long term working solution

Philip Robbins

It feels like two sheets would be a better solution here. One sheet is for the candidates and one sheet is for the reviewers. The reviewer sheet would have 5 columns for selecting candidates (Rank 1, Rank 2, etc…) and this would then be set up as a Dynamic View. The candidate sheet doesn't sound like it needs much information at all, so can sit on its own. Or if you wish, you could pull in a count of how many nominations they've had.

Paul Newcome

I would use a second sheet that has a form built into it that has candidate info, reviewer info, and rank. You can use the form URL in a formula to pre-fill the candidate info and drop this formula into another column on the main candidate sheet.

This will allow reviewers to click on the link to open a form that has the candidate info pre-filled. They enter their info and the rank they want to assign to the candidate, and this then gets submitted to the second sheet.

Back on the first sheet you would use formulas with cross sheet references (index/collect) to pull in each candidates rank for each reviewer.

A DV will allow reviewers to access the attachments and list of candidates, but also allow you to restrict it to only show the candidate and form entry columns.

vakhanka

Thank you very much, everyone. For context I have about 20 different awards each with its own Nomination Worksheet that contains a bunch of eligibility info and nomination packets. So the process below is replicated to each

Funnily enough, I built yesterday pretty much the exact thing that @Paul Newcome recommended. The overall solution is a little bit clunky, but the good thing is I think this way, I will have to make minimal adjustments next year when this process runs again.

1. I made a report for each award that pulls candidate names and is set to filter out by "Created" is in the past 120 days (nomination period). This is so that it will work the same next year.
2. I created a secondary worksheet for each award doing the data shuttle to transfer candidate names to 5 drop down menu columns. Then I made a form where the drop down column fields are labeled "First Choice," "Second Choice," "Third Choice," etc. I will then do some basic formulas that convert "X Choice" to a numeric value and avg these values and send them back to the nomination worksheet.
3. I also made a sheet where one column lists the awards, the next the committee members, then there is a column where I drop the hyper link to a published report and then the next column has a link to the form.
4. I made a Dynamic View limited by the 2024 Committee Members column. Next year I can just add another column called 2025 Committee members and change the dynamic view to filter by it. Now when a committee member logs in, they will see

It's a bit of a lift to set this up for 20 awards, but I think once it's set up this year, I won't have to fuss around with it too much in the following years.

Thank you again for your input.

Paul Newcome

Happy to help. 👍️