How can I link/stick a range from Sheet1 and a column from a Sheet2? [closed]

Question

I have a spreadsheet test here.

My project is a bit long (1000+ rows) but I created something that works in a similar way.

My hypothetical situation:

I receive grades from my students via Google Forms and one question of this Google Forms the students are allowed to put more than one grade. A spreadsheet of these responses is created.

I receive these grades in a Sheet1 but inside of a Sheet2 I have a CUSTOMSPLIT() formula/script that separates each line from the Sheet1 that has more than one grade.

In Sheet2, I have a control system of each grade for each student. There is a data validation with the options CHECKED and REGISTERED. The option REGISTERED is automatically placed when there is a new answer from a student and the option CHECKED I put/chose whenever is necessary.

The problem:

I can't link/arrange my data in alphabetical order in Sheet1 because the Sheet2 is be messed up. All the options CHECKED/REGISTERED from Sheet2 will be changed and will not follow the student/grade that I checked.

Anyone have any idea on how I can exit this problem?

Answer 1

You have a custom formula on Sheet 2 which appends form response data from from Sheet1, and splits any comma separated values. You have an additional column (Status) on sheet2 which is populated by an IF statement but which you can update to a different value based on Data Validation. You can't sort the data on Sheet2 without losing any association between the status and the form response.

A better way to do this is to re-write your function so that:

• it is triggered by a new form response using 'onformsubmit`, and
• it appends new form data to Sheet2, and
• either:
  • it automatically adds a status of "Registered" which you can change to any other Data Validation value whenever you like.
• or:
  • no status is assigned by the function (OR by a formula). Rather you manually assign the status.