I have a form where users can input their scores for several different tasks, how do I consolidate?

anartia

To keep the intake process simple, users will select from a drop down which task they are providing a score on. Users will input scores on a monthly and quarterly basis. On the back-end, the result is a sheet with many columns for the different tasks with and rows to match the task, but in the whole row only 1 cell has information. I need to create a consolidated table where all of the tasks are listed in one column with all of the matching scores listed in another column. Is there a way to do this that involve manually linking cells each time a user inputs their score?

Georgie

Hi @anartia,

It sounds like you’d like to create a separate sheet to consolidate all scores for each task so they can be easily viewed in one place. You can use formulas to do this. 

Assuming that your intake sheet looks similar to the one I created (below), you can use JOIN(COLLECT) formulas to consolidate all scores for each task.

To do so, you’d set up a sheet as you described - with all Task Names listed in one column. In the second column, you’ll create your formulas with cross sheet references. So, for Task 1, you could use =JOIN(COLLECT({scores task 1}, {scores task 1}, @cell <> ""), ", ")

The {scores task 1} reference in the formula above is the entire Task 1 column in your intake sheet - when you create a cross-sheet reference, you can name it as you wish. The formula says “Collect all scores in the Task 1 column on the intake sheet, excluding any cells in that column that are blank, and place a comma and a space in between the scores”. 

You can then copy this formula down to all other tasks, but you’ll need to remove both instances of the {scores task 1} reference and replace them with new cross-sheet references for the relevant task’s column in the intake sheet. Your sheet with your formulas should then look something like this:

If I’ve misunderstood what you’re asking for, could you share a screenshot of your intake sheet and provide a bit more information about what you’re looking to see in your consolidated table?

Thanks,

Georgie